topic Group by Interval Time in QlikView

Group by Interval Time

inigoelv — Sat, 16 Nov 2024 19:22:29 GMT

Good morning,

I have a question concerning to Interval time.

I have a table of consume like this:

Supplier	Fecha
A	01/01/2020 03:45
A	01/01/2020 05:30
B	01/01/2020 06:56
A	01/01/2020 09:00
A	01/01/2020 10:34
B	01/01/2020 11:23

What I pretend is a table like this:

Supplier	From	To
A	01/01/2020 03:45	01/01/2020 06:56
B	01/01/2020 06:56	01/01/2020 09:00
A	01/01/2020 09:00	01/01/2020 11:23
B	01/01/2020 11:23	.......

I imagine the solution will be with intervalmatch, but I can not solve this grouping.

Thanks in advance.

Re: Group by Interval Time

Taoufiq_Zarra — Mon, 30 Dec 2019 12:45:53 GMT

what is the rule for choosing to and from ?

Re: Group by Interval Time

sunny_talwar — Mon, 30 Dec 2019 14:05:37 GMT

May be this

Table: LOAD * INLINE [ Supplier, Fecha A, 01/01/2020 03:45 A, 01/01/2020 05:30 B, 01/01/2020 06:56 A, 01/01/2020 09:00 A, 01/01/2020 10:34 B, 01/01/2020 11:23 ]; tmpTable: LOAD DISTINCT Supplier, If(Supplier = Previous(Supplier), Previous(Fecha), Fecha) as From Resident Table Order By Fecha; Table: LOAD DISTINCT Supplier, From, Alt(Peek('From'), '12/31/9999 11:59') as To Resident tmpTable Order By From Desc; DROP Table Table, tmpTable;

Re: Group by Interval Time

Brett_Bleess — Tue, 21 Jan 2020 20:17:34 GMT

Did Sunny's example get you what you needed? If so, please do not forget to return to your thread and use the Accept as Solution button on his post if so, that gives him credit for the assistance and it also lets the other Community Members know it did work too. If you still need further help, leave an update post.

Regards,
Brett

Re: Group by Interval Time

Saravanan_Desingh — Wed, 20 May 2020 20:59:29 GMT

One solution is.

tab1: LOAD *, If(Peek('Supplier')=Supplier, Peek('K1'), RangeSum(Peek('K1'),1)) As K1 , Peek('K1') As K2 ; LOAD Supplier, Timestamp#(Fecha,'M/D/YYYY hh:mm') As Fecha INLINE [ Supplier, Fecha A, 1/1/2020 3:45 A, 1/1/2020 5:30 B, 1/1/2020 6:56 A, 1/1/2020 9:00 A, 1/1/2020 10:34 B, 1/1/2020 11:23 ]; Left Join (tab1) LOAD K1, TimeStamp(Min(Fecha)) As From Resident tab1 Group By K1 ; tab2: LOAD K2, TimeStamp(Max(Fecha)) As To Resident tab1 Group By K2 ; Left Join(tab1) LOAD K2 As K1, To Resident tab2; Drop Table tab2;

Re: Group by Interval Time

Saravanan_Desingh — Wed, 20 May 2020 21:00:45 GMT