Unpivot and Pivot

jp2631

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!

trafalger

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:

https://community-forums.domo.com/main/discussion/50754/etl-magic-join-on-date-range

MarkSnodgrass

Agree with @trafalger . Here is a video on how to do it:

https://youtu.be/9iNpzSL5V6U

trafalger

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:

https://community-forums.domo.com/main/discussion/50754/etl-magic-join-on-date-range

MarkSnodgrass

Agree with @trafalger . Here is a video on how to do it:

https://youtu.be/9iNpzSL5V6U

jp2631

@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.

trafalger

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.

jp2631

@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