Load using flag field

[Teja2]

hi,

i have a table as below 

1)how can i compare old data of ID=PRD858 and set flag as OLD and new ID=PRD858 and set flag as NEW

2)only ID=PRD858  & Purchsed_Date=23/6/20 should be old remaining data should refer as NEW

Please help 

ID	Product	Purchased_Date	Region	Sales	Flag
PRD258	Pen Drive	04-02-20	N	160	OLD
PRD358	Mobile	23-09-20	E	161	OLD
PRD458	Harddisk	17-01-20	S	164	OLD
PRD558	Pen Drive	16-05-20	W	134	OLD
PRD658	Mobile	05-10-20	N	135	OLD
PRD758	Harddisk	07-05-20	E	162	OLD
PRD858	Pen Drive	23-06-20	S	131	OLD
PRD858	Pen Drive	01-01-21	S	131	NEW
PRD958	Mobile	07-02-20	W	148	OLD
PRD1058	Harddisk	22-11-20	N	142	OLD
PRD1158	Harddisk	21-06-20	E	136	OLD
PRD1458	Mobile	02-02-21	N	121	NEW

[Saravanan_Desingh]

As per #2, the first record should not be OLD right?

Sorry I could not understand your requirement

[Teja2]

hai sorry for confusion

old record ID prd858 purchased date 23/06/20 should record as old and
ID prd858 purchased date 01/01/21 should show as new and all remaining
records should record as new as well

[MayilVahanan]

Hi Teja2,

May be try like below

Temp:
LOAD * INLINE [
ID, Product, Purchased_Date, Region, Sales
PRD258, Pen Drive, 04-02-20, N, 160
PRD358, Mobile, 23-09-20, E, 161
PRD458, Harddisk, 17-01-20, S, 164
PRD558, Pen Drive, 16-05-20, W, 134
PRD658, Mobile, 05-10-20, N, 135
PRD758, Harddisk, 07-05-20, E, 162
PRD858, Pen Drive, 23-06-20, S, 131
PRD858, Pen Drive, 01-01-21, S, 131
PRD958, Mobile, 07-02-20, W, 148
PRD1058, Harddisk, 22-11-20, N, 142
PRD1158, Harddisk, 21-06-20, E, 136
PRD1458, Mobile, 02-02-21, N, 121
];

Join
Load PD where NoOfProd = 2;
Load ID, Max(Purchased_Date) as PD, Count(Product) as NoOfProd Resident Temp
Group by ID;

Final:
Load *, If(Purchased_Date >= PD, 'NEW', 'OLD') as Flag Resident Temp;

DROP Table Temp;