Combine 2 data set (One to many)

Atieh
Atieh Member
in Magic ETL

Hi Community,

I am struggling with combining two data set like below

image.png

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?

Tagged:

Best Answers

  • Angele
    Angele Member
    Answer ✓

    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

    image.png

  • JasonAltenburg
    JasonAltenburg Coach
    Answer ✓

    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`)
    image.png

    image.png
    image.png

    Note that if our values were $100, $110, $130 for the rows in c1, our beast mode would display $130.

    Hopefully this is helpful!

Answers

  • nmizzell
    nmizzell Contributor

    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.

    image.png

  • Atieh
    Atieh Member

    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

  • Atieh
    Atieh Member

    @nmizzell I did what you said but the other headers value like client name, page and ID would be empty when I do Append Rows

    Any idea how to fix this issue?

  • Angele
    Angele Member
    Answer ✓

    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

    image.png

  • JasonAltenburg
    JasonAltenburg Coach
    Answer ✓

    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`)
    image.png

    image.png
    image.png

    Note that if our values were $100, $110, $130 for the rows in c1, our beast mode would display $130.

    Hopefully this is helpful!

Categories