Case 945 How to pick the latest date for each Doc_Num ?

[paulyeo11]

Hi All

I have a raw data , for some doc number it have few date , may i know how to Flag the latest date ?

My raw data :-

Doc_num	SOURCE	Date
1	A	25-Sep-20
1	A	20-Sep-20
2	A	2-Sep-20
3	B	25-Sep-20
3	B	20-Sep-20
4	B	2-Sep-20

I have below load script :-

CRM:
LOAD * INLINE [
SOURCE,Doc_num,Date

A,1,25-Sep-20
A,1,20-Sep-20
A,2,2-Sep-20
B,3,25-Sep-20
B,3,20-Sep-20
B,4,2-Sep-20

];

My expected result :-

Doc_num	SOURCE	Date	Flag
1	A	25-Sep-20	Y
1	A	20-Sep-20
2	A	2-Sep-20	Y
3	B	25-Sep-20	Y
3	B	20-Sep-20
4	B	2-Sep-20	Y

Hope some one can advise me.

Paul

[Vegar]

Try this 

CRM:

LOAD * INLINE [

SOURCE,Doc_num,Date

A,1,25-Sep-20

A,1,20-Sep-20

A,2,2-Sep-20

B,3,25-Sep-20

B,3,20-Sep-20

B,4,2-Sep- 20];

Left join LOAD Doc_num, max(Date) as Date, 'Y' as Flag

Resident CRM

Group by Doc_num;

[paulyeo11]

Hi Sir

Thank you for sharing.

When i run below line :-

Left join Doc_num, max(Date) as Date, 'Y' as Flag

I get error :-

Unknown statement
Left join Doc_num, max(Date) as Date, 'Y' as Flag

Paul

[Vegar]

I missed a 'LOAD' in my suggestion. It should be

Left join LOAD Doc_num... 

[paulyeo11]

Hi sir

Now no more error  , But the Flag field does not work now.

Doc_num	SOURCE	Date	Flag
1	A	25-Sep-20
1	A	20-Sep-20
2	A	2-Sep-20
3	B	25-Sep-20
3	B	20-Sep-20
3	B	25-May-20
4	B	20-May-20
4	B	2-May-20
4	B	2-Sep- 20

Paul 

[Kushal_Chawda · Accepted solution]

@paulyeo11  It will not work unless you format your Date field as Date. Assuming your Date field values are in in DD-MMM-YY format. You can provide the format as per your actual data format in Date# function

CRM:
LOAD SOURCE,
     Doc_num,
     date(Date#(Date,'DD-MMM-YY')) as Date
FROm table;

Left join 
LOAD Doc_num, 
      date(max(Date)) as Date, 'Y' as Flag
Resident CRM
Group by Doc_num;

[paulyeo11]

Hi Kush

Thank you very much. it work very well.