Join Data in ETL
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?
Best Answers
-
Step 1: Create a new Magic ETL Transform with your two datasets
Step 2: Join the data on the Equality condition: student_id = student_id3. 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.If I solved your problem, please select "yes" above
0 -
Yes you can use the Alter Columns tile to drop the Country2 column.
If I solved your problem, please select "yes" above
0
Answers
-
Step 1: Create a new Magic ETL Transform with your two datasets
Step 2: Join the data on the Equality condition: student_id = student_id3. 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.If I solved your problem, please select "yes" above
0 -
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?
0 -
Yes you can use the Alter Columns tile to drop the Country2 column.
If I solved your problem, please select "yes" above
0 -
@ColemenWilson Thank you so much!
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 306 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 787 Beast Mode
- 78 App Studio
- 43 Variables
- 742 Automate
- 187 Apps
- 474 APIs & Domo Developer
- 67 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 139 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive