Join Data in ETL

giannzi · 2025-01-28T02:19:15+00:00

There was an error rendering this rich post.

I have two datasets: one for home country and another for citizenship. Both datasets share two common columns: country and student_id. In the home country dataset, the country column is 70% populated, leaving 30% of the rows as null. I want to fill these null values in the home country dataset using the corresponding country values from the citizenship dataset, based on the student_id. How can I achieve this?

Find more posts tagged with

Join

Magic ETL

Accepted answers

ColemenWilson

Step 1: Create a new Magic ETL Transform with your two datasets
Step 2: Join the data on the Equality condition: student_id = student_id

Screen Shot 2025-01-27 at 8.22.33 PM.png

3. Add an Add Formula tile with the following formula (your field names will likely be different):
COALESCE(country,Dataset 2.country)
4. Step 3 uses the country value if it is populated, if it is not, it populates it from the joined value from dataset 2.
5. Add an output dataset tile and save and run your dataflow.

ColemenWilson

Yes you can use the Alter Columns tile to drop the Country2 column.

All comments

ColemenWilson

Step 1: Create a new Magic ETL Transform with your two datasets
Step 2: Join the data on the Equality condition: student_id = student_id

giannzi

https://community-forums.domo.com/main/discussion/comment/100362#Comment_100362

Hi @ColemenWilson , thank you for your answer. When I did it, there were 2 columns: Country and Country2. Is it possible to only have 1 column: the Country?

ColemenWilson

Yes you can use the Alter Columns tile to drop the Country2 column.

giannzi

@ColemenWilson Thank you so much!