Exclude ID's contains last two years date

[karan_kn]

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
];

[Frank_Hartmann]

see attached.

hope this helps

[karan_kn]

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

[Frank_Hartmann · Accepted solution]

see attached

[rajaxavier]

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;