Grab value from one column and add it to another dataset?

dkonig
dkonig Member
in Magic ETL

This is probably a simple question but it eludes me since I don't use DOMO regularly.

I have 3 datasets. A, B, and C

Dataset 'A' has all the metrics for a specific ad set ID but no information for the campaign. No campaign ID or campaign name.

Dataset 'B' has all the high level details for the ad sets which include the ad set ID and a campaign ID but does not include the campaign name which is what I am ultimately after.

Dataset 'C' has all the high level details for the campaigns. This includes the campaign ID and the campaign name.

In my head, what I think I am attempting to do

  1. Add a 'Campaign Name' column to dataset 'A'.
  2. Take the ad set ID from dataset 'A' and lookup in dataset 'B' to get the campaign ID
  3. Take the campaign ID and lookup in dataset 'C' to get the campaign name
  4. Write the campaign name to the new 'Campaign Name' column in dataset 'A'

What is the best way to do this? I can visualize the process going from dataset to dataset picking up a little more information each time and then writing it to the new column but I'm just not sure how to execute.

Tagged:

Answers

  • ColemenWilson
    ColemenWilson Coach

    This can all be done in a single ETL. Just go through the flow as you have in your head in Magic ETL and add the inputs along the way joining them as you go. You got this! Let us know if you get stuck!

    If I solved your problem, please select "yes" above

  • MarkSnodgrass
    MarkSnodgrass Coach

    Your ETL will look something like this where you are linking multiple join tiles one after another.

    image.png

    In your case, you would start with dataset B since that has the IDs for everything. Next, add a join tile and connect to dataset A and join on Ad ID. Next, add a join tile and connect to dataset C and join on campaign ID. You can then use the Select Columns or Alter Columns tile to remove columns you don't need.

    You will also want to determine if you should do a left join or inner join, depending if you need to carry everything forward or only if the exist in each table.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • dkonig
    dkonig Member

    Thanks. I think I may have figured it out. I guess the nomenclature they use for joins where you are adding rows didn't quite make sense to me when all I wanted to do was add and alter a column. I am doing some data validation now to see if I got the result I expected. I'll be back if I have any other questions.

Categories