Join Data in ETL

giannzi · January 28

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?

ColemenWilson · January 28

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 · January 29

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

ColemenWilson · January 28

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

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.

giannzi · January 29

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 · January 29

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

giannzi · January 29

@ColemenWilson Thank you so much!

Join Data in ETL

Best Answers

Answers

Categories