Unpivot and Pivot
Happy Friday! I'm not sure how to start. The data in the yellow section is what I currently have in the magic ETL. I want the result to look like the data in the green section. I've tried to Unpivot the data and Pivot them but have been unsuccessful. Could someone shed some light on this issue? Thank you very much!
Best Answers
-
In this case I'd join to the date dimension, find all of the days between TimeOffDate and ReturnToWork and split the HoursOff between them.
Similar to this:
1 -
Agree with @trafalger . Here is a video on how to do it:
**Check out my Domo Tips & Tricks Videos
**Make sure to
any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
In this case I'd join to the date dimension, find all of the days between TimeOffDate and ReturnToWork and split the HoursOff between them.
Similar to this:
1 -
Agree with @trafalger . Here is a video on how to do it:
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@trafalger and @MarkSnodgrass I tried the above suggestion, but it doesn't seem to work. Regarding the formula below, I attempted to filter out the "dt" dates that are not between "TimeOffDate" and "LastAbsenceDate" (ReturnToWork), but I still ended up getting them. I'm uncertain about how to distribute the "HoursOff" between these dates after calculating the days in between.
Thank you all for your help and support.
0 -
Why are you using the LAST_DAY beast mode here? That will make all of the dates the last day of the month that they're in.
0 -
@trafalger Must have been a late night of work, as the brain no longer functions, but I got my problem resolved. Thanks to you and @MarkSnodgrass
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive