QlikView Running PivotTable

rckomma4119 — Wed, 25 Jul 2018 21:31:58 GMT

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

Re: QlikView Running PivotTable

felipedl — Thu, 26 Jul 2018 13:04:53 GMT

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)?

Re: QlikView Running PivotTable

rckomma4119 — Thu, 26 Jul 2018 13:19:38 GMT

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

Re: QlikView Running PivotTable

felipedl — Thu, 26 Jul 2018 13:41:36 GMT

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.

Re: QlikView Running PivotTable

felipedl — Thu, 26 Jul 2018 17:14:41 GMT

See the attached QVW i sent for a possible solution.

Re: QlikView Running PivotTable

rckomma4119 — Thu, 26 Jul 2018 18:17:51 GMT

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.

Re: QlikView Running PivotTable

rckomma4119 — Fri, 27 Jul 2018 19:03:52 GMT

Felip, this solution worked, appreciate it.

Re: QlikView Running PivotTable

felipedl — Fri, 27 Jul 2018 20:01:45 GMT

Glad it helped .

topic Re: QlikView Running PivotTable in QlikView

QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable

Re: QlikView Running PivotTable