Combine 2 data set (One to many)

Atieh · 2024-08-27T22:16:26+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Magic ETL

Accepted answers

Angele

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

JasonAltenburg

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!

All comments

nmizzell

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.

Atieh

Thanks @nmizzell ,

if I do that then the value of the spend would get in the value of times right?

So then would I need to unpivot them? hmm trying to understand how this will work all together