Using Magic ETL For One To Many
I have one dataset with many, many lines with a company number. I have another dataset with one line per company with plan values in it. I want to combine the two in Magic but don't want the Plan number to repeat over and over (flatten the plan?) in the combined dataset so that the summarized number per company number in the subsequent dataset equals the plan file with one row per company number.
Hopefully this make sense. I thought I was taught at one point how to do this in Magic, but drawing a blank.
Thanks,
DK
Comments
-
Hi DK,
are the "many, many lines" duplicate values? If so, you might want to try the Remove Duplicates function in ETL.
If they are not: which values of the larger dataset do you want to keep? If it is, say, a dataset with a timestamp that gives you values for each company for each day, you can use Rank and Window to, say, give the most recent value a rank of 1 and then Filter Rows to only keep those. Join on company ID and you should have what you want.
If you need aggregations, like the sum of all values for each company, I would suggest you use a MySQL dataflow with something like
SELECT CompanyID, SUM(value) FROM dataset
GROUP BY CompanyID;
and then just join the oneline dataset to it using LEFT JOIN.
HTH
0 -
Is anyone able to help out with this request?
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 209 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 18 Automate
- 355 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive