Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can i get the below desired result in a pivot table. My fact table only has in-time and Out-time. But, I need to count the cases along the duration of the case.
Saturday - Rooms Running | |||||||||||||||||||||||||||||||||||||||||
Date | Total Cases | 7:15 | 7:30 | 7:45 | 8:00 | 8:15 | 8:30 | 8:45 | 9:00 | 9:15 | 9:30 | 9:45 | 10:00 | 10:15 | 10:30 | 10:45 | 11:00 | 11:15 | 11:30 | 11:45 | 12:00 | 12:15 | 12:30 | 12:45 | 13:00 | 13:15 | 13:30 | 13:45 | 14:00 | 14:15 | 14:30 | 14:45 | 15:00 | 15:15 | 15:30 | 15:45 | 16:00 | 16:15 | 16:30 | 16:45 | 17:00 |
6/30/2018 | 8 | - | - | 1 | 1 | 1 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | - | - | - | - | - | - | - | - | 1 | 1 | 1 | 1 | 1 | - | - |
6/23/2018 | 8 | 1 | - | 1 | - | - | - | - | 1 | 2 | 2 | 2 | 2 | 2 | 1 | - | - | 1 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | - | - | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
6/16/2018 | 8 | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | - | - | - | - | 1 | 1 | 1 | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | - | - | 1 | 1 | 1 | 1 |
6/9/2018 | 7 | - | - | 2 | 1 | 1 | 1 | 1 | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | - | - | - | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
6/2/2018 | 3 | - | - | 1 | 1 | 1 | 1 | 1 | - | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
5/26/2018 | 6 | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | - | - | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
FACT | |||
Date | Case | InTime | Out Time |
06/30/2018 | A | 7:33 | 10:52 |
06/30/2018 | A | 8:16 | 9:01 |
06/30/2018 | B | 10:10 | 12:53 |
06/30/2018 | C | 10:35 | 23:03 |
06/30/2018 | C | 11:32 | 12:12 |
06/30/2018 | A | 12:02 | 12:46 |
06/30/2018 | D | 12:40 | 13:24 |
06/30/2018 | C | 15:19 | 16:32 |
06/30/2018 | B | 21:41 | 22:24 |
Time Dim | |||
Time | Hour | Min | |
7:00 | 7 | 0 | |
7:01 | 7 | 1 | |
7:02 | 7 | 2 | |
7:03 | 7 | 3 | |
7:04 | 7 | 4 | |
7:05 | 7 | 5 | |
7:06 | 7 | 6 | |
7:07 | 7 | 7 | |
7:08 | 7 | 8 | |
7:09 | 7 | 9 | |
7:10 | 7 | 10 | |
7:11 | 7 | 11 | |
7:12 | 7 | 12 | |
7:13 | 7 | 13 | |
7:14 | 7 | 14 |
Appreciate your response
Not the prettiest approach, but see the attached file and QVW for a solution.
I've altered your dimension and used intervalmatch to create each individual time line needed.
Whats the logic behind the count?
If there's a class running from 9:03 to 9:33 it should be counted as four periods (9h~9:15;9:16~9:30;9:31~9:35)?
it should be counted as 3 periods (9:15, 9:30 & 9:45).
Logic is basically the count(cases) by time slot duration (OutTime - InTime).
Not the prettiest approach, but see the attached file and QVW for a solution.
I've altered your dimension and used intervalmatch to create each individual time line needed.
See the attached QVW i sent for a possible solution.
Thanks Felip. This is a workable solution and really appreciate your help. I am currently modifying my data sets to make them fall in line with your model. I will update you soon.
Felip, this solution worked, appreciate it.
Glad it helped .