Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?