I am working on a report where the data looks like below (sample data):
Services : JAS, GAF and YAG fall under the profit center "ABC" and HIG and OPD falls under "XYZ"
Date | Profit Center | Service |
|---|---|---|
1/1/24 | ABC | |
1/1/24 | JAS | |
1/1/24 | GAF | |
1/1/24 | YAG | |
1/1/24 | XYZ | |
1/1/24 | HIG | |
1/1/24 | OPD |
I want my final output to look like below, there should be a profit center for each service in each row.
Date | Profit Center | Service |
|---|---|---|
1/1/24 | ABC | XCS |
1/1/24 | ABC | JAS |
1/1/24 | ABC | GAF |
1/1/24 | ABC | YAG |
1/1/24 | XYZ | HIG |
1/1/24 | XYZ | OPD |
Can someone please guide me how can we do that in domo etl ? I know that window functions (lag,lead) aren't supported in Domo's mysql, so trying to find a solution with magic etl.
Thankyou
I think you want to fill down the Profit Center column, and once you've created a filled down Profit Center column, you could filter out the blank Service rows.
Here's a good explanation from @MichelleH on filling down using Group-By and Coalesce in MagicETL:
I also like the Python implementation from @user01506 (except that Python in a MagicETL feels like putting on a lobster bib at a Denny's)
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
Do the services map to profit centers? For example, is XCS always in the ABC profit center? How many total services & profit centers are there? Are there any other fields that group profit centers together?
If I solved your problem, please select "yes" above
@SS1108 If the service centers always map to the same profit center you could use a mapping table that joins into the ETL or you can do a beastmode/formula tile like below
case
when 'service' in ('XCS', 'JAS', 'GAF', 'YAG') then 'ABC'
when 'service' in ('HIG','OPD') then 'XYZ'
end
**If this answer solved your problem be sure to like it and accept it as a solution!
I think you want to fill down the Profit Center column, and once you've created a filled down Profit Center column, you could filter out the blank Service rows.
Here's a good explanation from @MichelleH on filling down using Group-By and Coalesce in MagicETL:
I also like the Python implementation from @user01506 (except that Python in a MagicETL feels like putting on a lobster bib at a Denny's)
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
@DavidChurchman the explanation from @MichelleH was really helpful. I was able to successfully map the data. Thank you so much
