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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive