Look up from google drive to combine and aggregate data from multiple source files

Answered

Divya Navakumar

• June 06, 2022 18:40

Hello,

I have the following use case. I have employee information, employee training information, and employee payroll information coming in three separate files. I am trying to load files from google drive and trying to find various aggregations on the total data set.

Example: Total employee hours spent on each training, Gender-based break up for training data, Age group, gender-based payroll data, etc. 

I am running into these problems in the design - 

1. Look up from google drive only has only "look-up additional files per record" and there does not seem to have an option to merge records back with the original source record.
2. If I bring in all the files as exports instead of look-ups, based on various community posts and articles, it looks like all the sources should have the same fields for the flows to process correctly. I could not find any ways to merge records based on a common field or unique IDs. 
3. The javascript functions which can be used to aggregate the entire data set has limitations based on page sets and can only aggregate the data set per page. This could lead to incorrect and undesirable results if there is no way to ensure the data can be delivered as single-page data. 

Could you please suggest how I can solve this issue? If you could point me to any relevant resources it would be great. 

Thanks,

Divya

 

0

• 

  Scott Henderson CTO

  • June 06, 2022 20:51

  

  

  I would recommend using integrator.io flows to load all the different data sources into a SQL database, and then you can use SQL to aggregate and/or join the data any way you like, and then if you want to sync the results of your queries back into your cloud apps, then it is super easy to build these "reverse etl" type flows too.

  There is no way to merge and aggregate different files together using only integrator.io flows, but flows can definitely help you get data from external files in a google drive folder into a SQL database, and then from a SQL database back into cloud apps or other files, etc...

  0
• 

  Divya Navakumar

  • June 18, 2022 18:31

  

  

  Thank you, Scott 

  0

Please sign in to leave a comment.