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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive