Combine 2 data set (One to many)
Hi Community,
I am struggling with combining two data set like below
As you can see we have one campaign but has many views. When I combine these two, spend would get allocated to all the rows in table 1. How can I join these two table without overcounting the data on table 2?
Best Answers
-
I don't think you have any way to fix it because you have no value in table 2 corresponding to Client name, ID, …
if you really what to join those 2 tables you will have empty rows .
To be able to join 2 data set like that you should have all fields available to avoid any empty cells as this dataset I shared below.
if you find any other solution please share with us
1 -
Hi @Atieh can you please describe what your desired output might look like?
Some options I'm thinking about based on what you have typed:
Would you like to divide the spend across the multiple rows on table 1?
… or would you like to append the row from table 1 with a unique identifier so that you don't get inflated spend numbers? and if so, what would be the expected result of filtering on a date, say 7/21?
Alternatively, if you join those metrics and ended up with spend on every row, you could use beast modes to de-duplicate, check out the following beast mode that handles this:
MAX(`Spend`) OVER (PARTITION BY `Campaign`)
Note that if our values were $100, $110, $130 for the rows in c1, our beast mode would display $130.
Hopefully this is helpful!1
Answers
-
Hey Atleh,
You will need to use the 'Append Rows' magic etl tile to accomplish this.
Before appending the rows of the two tables together, make sure that the column names are consistent. For example, rename the 'Time spent (sec)' column on table 1 to 'Spend'. This will tell domo to align the new combined table on the columns with the same name.
You can use the 'select columns' tile to rename columns.
0 -
I don't think you have any way to fix it because you have no value in table 2 corresponding to Client name, ID, …
if you really what to join those 2 tables you will have empty rows .
To be able to join 2 data set like that you should have all fields available to avoid any empty cells as this dataset I shared below.
if you find any other solution please share with us
1 -
Hi @Atieh can you please describe what your desired output might look like?
Some options I'm thinking about based on what you have typed:
Would you like to divide the spend across the multiple rows on table 1?
… or would you like to append the row from table 1 with a unique identifier so that you don't get inflated spend numbers? and if so, what would be the expected result of filtering on a date, say 7/21?
Alternatively, if you join those metrics and ended up with spend on every row, you could use beast modes to de-duplicate, check out the following beast mode that handles this:
MAX(`Spend`) OVER (PARTITION BY `Campaign`)
Note that if our values were $100, $110, $130 for the rows in c1, our beast mode would display $130.
Hopefully this is helpful!1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive