topic Re: Exclude ID's contains last two years date in QlikView

Exclude ID's contains last two years date

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

Need to pick ID only with has not purchased last 2 years (<04/12/2018).

LOAD * INLINE [
ID, Purchase_Date
1, 4/13/2017
1, 12/1/2018
1, 7/25/2019
2, 3/10/2020
3, 2/21/2020
3, 10/11/2017
3, 10/15/2019
3, 10/5/2017
4, 1/5/2017
4, 5/8/2017
5, 7/3/2016
5, 12/6/2017
6, 7/6/2018
6, 1/18/2020
7, 11/27/2017
7, 3/27/2018
8, 10/5/2019
8, 12/6/2017
8, 8/4/2017
];

Re: Exclude ID's contains last two years date

Frank_Hartmann — Mon, 13 Apr 2020 12:32:10 GMT

see attached.

hope this helps

Re: Exclude ID's contains last two years date

karan_kn — Mon, 13 Apr 2020 14:22:32 GMT

Thanks for the response, can we exclude ID's in the load statement?

Re: Exclude ID's contains last two years date

Frank_Hartmann — Mon, 13 Apr 2020 15:07:26 GMT

see attached

Re: Exclude ID's contains last two years date

rajaxavier — Sun, 26 Apr 2020 16:26:00 GMT

Tab:
LOAD * INLINE [
ID, Purchase_Date
1, 4/13/2017
1, 12/1/2018
1, 7/25/2019
2, 3/10/2020
3, 2/21/2020
3, 10/11/2017
3, 10/15/2019
3, 10/5/2017
4, 1/5/2017
4, 5/8/2017
5, 7/3/2016
5, 12/6/2017
6, 7/6/2018
6, 1/18/2020
7, 11/27/2017
7, 3/27/2018
8, 10/5/2019
8, 12/6/2017
8, 8/4/2017
];

Temp:
Load
floor(Date(Date#(Addyears(yearstart(max(Purchase_Date)),-1),'MM/DD/YYYY'),'DD/MM/YYYY')) as last2year_date
Resident Tab ;

let vdate = peek('last2year_date',0,'Temp');

Drop Table Temp;

Tab1:
Load
ID as Id,
floor(Date(Date#(Purchase_Date,'MM/DD/YYYY'),'DD/MM/YYYY')) as Purchase_Date
Resident Tab where Purchase_Date >= $(vdate);

NoConcatenate

Tab2:
Load
ID,
floor(Date(Date#(Purchase_Date,'MM/DD/YYYY'),'DD/MM/YYYY')) as Purchase_Date
Resident Tab where not exists (Id,ID);

Drop Tables Tab,Tab1;