Can I make a "mother row" to assign values from?

ljb18

We are a music library company, and as such a large amount of our income comes in as small royalty payments that add up incrementally. The information comes from 15+ different collection sources, and in general the only thing consistent between them all is the track title. That means that the information from the royalty reports is missing data such as composer information, key words, instrumentation, genre, etc. However, when we send out the albums for publication we fill out a huge metadata spreadsheet that has all this information.

What I'd like to do is somehow use the metadata spreadsheet to assign values to the royalty sheets. For example, if the "metadata track title" = "royalty track title", then I assign the values from the metadata rows to the spreadsheets. Is this possible? I've tried a handful of different things with the formula tile and other things, but what I keep running into is that one row can't be compared against another row; it's always only comparing columns against columns. Right now, my only work around is to manually enter the information for every single track:

[name created from new formula column is `Composer`]

CASE

WHEN `Track Title` = '[Track Name]' then '[Composer]'

else null end

I'd have to do it like this for every single bit of information I'd like to add, such as: Composer 2, Composer 3, Composer 4, Composer 5, sometimes even Composer 6, key words, mood, genre, uses, instrumentation, and more.

Obviously, that is a huge pain and after dozens and dozens of hours are spent catching up the rest of the catalogue, it will still require that I update the information every single time we release a new album. So is there a way that I can just pull the information from the metadata sheet? The information is already there, I just wish I could figure out how to pull and place it where it needs to go. Thank you!

MarkSnodgrass

You can combine these two datasets very easily in Magic ETL using the Join tile. In Magic ETL, add both datasets (your royalty dataset and your metadata dataset) as input datasets. Then add the join tile and join them on track title. You can then use the select columns tile to select the columns from the metadata dataset and have all the information you need. If you are unfamiliar with the join tile, you can read more about here:

https://domohelp.domo.com/hc/en-us/articles/360042923674-Understanding-Joins

https://domohelp.domo.com/hc/en-us/articles/360044876194-New-Magic-ETL-Tiles-Combine-Data#3.

Hope this helps.

MarkSnodgrass

You can combine these two datasets very easily in Magic ETL using the Join tile. In Magic ETL, add both datasets (your royalty dataset and your metadata dataset) as input datasets. Then add the join tile and join them on track title. You can then use the select columns tile to select the columns from the metadata dataset and have all the information you need. If you are unfamiliar with the join tile, you can read more about here:

https://domohelp.domo.com/hc/en-us/articles/360042923674-Understanding-Joins

https://domohelp.domo.com/hc/en-us/articles/360044876194-New-Magic-ETL-Tiles-Combine-Data#3.

Hope this helps.

ljb18

Thank you!! I didn't realize that's what the join data tile did... I was using the combine rows and that clearly was the wrong move. I've been able to do exactly what I've been wanting to do, so thank you very much!