topic Re: Aggr on date in QlikView

Aggr on date

Tue, 18 Nov 2014 10:15:57 GMT

Hi all, I have the folliwing table:

Name	Type	DateTime
John	A	01/01/2015 10:00:00
John	A	02/01/2015 10:30:00
John	B	04/01/2015 18:00:00
John	A	05/01/2015 16:30:00
John	B	07/01/2015 09:00:00
John	B	08/01/2015 11:00:00
John	A	11/01/2015 13:00:00
Mary	A	02/01/2015 12:00:00
Mary	A	05/01/2015 19:00:00
Mary	B	08/01/2015 14:00:00
Mary	B	11/01/2015 08:00:00
Mary	B	13/01/2015 20:00:00

the goal is to count for each 'Name' of 'Type' = 'A', how many items of type 'B' do we have within 7 days starting from 'DateTime'.

Example: in the first row datetime + 7 days = 08/01/2015 10:00:00 -> how many rows with name John, type B and DateTime < 08/01/2015 10:00:00 do I have? the answer is 2 rows (the 3rd one and the 5th one since the 6th one has a DateTime beyond 7 days)

I hope I have been clear, I attached a qvw file with the desired results.

I thank you in advance for your help.

Andreos

Re: Aggr on date

anbu1984 — Tue, 18 Nov 2014 10:46:00 GMT

SourceTable:

LOAD 1 As Key,*,Date(DateTime + 7, 'DD/MM/YYYY hh:mm:ss') as DateTime_plus7days INLINE [

Name, Type, DateTime

John, A, 01/01/2015 10:00:00

John, A, 02/01/2015 10:30:00

John, B, 04/01/2015 18:00:00

John, A, 05/01/2015 16:30:00

John, B, 07/01/2015 09:00:00

John, B, 08/01/2015 11:00:00

John, A, 11/01/2015 13:00:00

Mary, A, 02/01/2015 12:00:00

Mary, A, 05/01/2015 19:00:00

Mary, B, 08/01/2015 14:00:00

Mary, B, 11/01/2015 08:00:00

Mary, B, 13/01/2015 20:00:00

];

Join(SourceTable)

LOad 1 As Key,Name,DateTime As DateTimeB Resident SourceTable Where Type = 'B';

NoConcatenate

Final:

Load Name, Type, DateTime, DateTime_plus7days, Count(If(Type = 'A' And (DateTimeB >= DateTime And DateTimeB <= DateTime_plus7days),1)) As Cnt Resident SourceTable

Group by Name, Type, DateTime, DateTime_plus7days;

DROP Table SourceTable

Re: Aggr on date

Tue, 18 Nov 2014 10:55:52 GMT

Thanks anbu, it works great. In your opinion is there a way to obtain the same result using the aggr function or something else but without acting on the script and change the data structure?