Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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;
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;
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
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
@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;
Hi Kush
Thank you very much. it work very well.