Fill empty columns based on values in other columns
I'm trying to figure out a way to do the following in an ETL.
Given data that looks like this, I want to propagate the email address from the first row with a specific ID to all other rows that match that ID. I need to do this in order to attribute all interactions of all sorts to a specific user's email address:
Input Example:
Desired Output:
Best Answers
-
This is best accomplished in a dataflow. Using magicETL you can use the group by tile and group by ID for the field Email and select the first non-null value option, then, with 1 email per id from the output of that tile, you can join that with you data on the ID field. Let me know if you have any questions!
If I solved your problem, please select "yes" above
1 -
No because you will break out the group by into a separate line in the dataflow. So from you input dataset there will be two lines:
1. All rows of data2. 1 unique row for each ID.
Then you will left join the unique rows for each ID with the original input that has all rows of data. No rows of data will be lost.
If I solved your problem, please select "yes" above
1
Answers
-
This is best accomplished in a dataflow. Using magicETL you can use the group by tile and group by ID for the field Email and select the first non-null value option, then, with 1 email per id from the output of that tile, you can join that with you data on the ID field. Let me know if you have any questions!
If I solved your problem, please select "yes" above
1 -
Thanks @colemenwilson I'm going to try that approach and will post back if I can't figure it out.
0 -
@colemenwilson - if I do the group by ID tile, won't I lose the additional rows for that same ID? The ID is in the same table as the individual interaction rows
0 -
No because you will break out the group by into a separate line in the dataflow. So from you input dataset there will be two lines:
1. All rows of data2. 1 unique row for each ID.
Then you will left join the unique rows for each ID with the original input that has all rows of data. No rows of data will be lost.
If I solved your problem, please select "yes" above
1 -
Got it, thanks!
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 791 Beast Mode
- 78 App Studio
- 43 Variables
- 753 Automate
- 187 Apps
- 478 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive