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.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive