Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

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_numSOURCEDate
1A25-Sep-20
1A20-Sep-20
2A2-Sep-20
3B25-Sep-20
3B20-Sep-20
4B2-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_numSOURCEDateFlag
1A25-Sep-20Y
1A20-Sep-20 
2A2-Sep-20Y
3B25-Sep-20Y
3B20-Sep-20 
4B2-Sep-20Y

Hope some one can advise me.

Paul

1 Solution

Accepted Solutions
Kushal_Chawda

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

 

View solution in original post

6 Replies
Vegar
MVP
MVP

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
Master
Master
Author

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
MVP
MVP

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

Left join LOAD Doc_num... 

paulyeo11
Master
Master
Author

Hi sir

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

Doc_numSOURCEDateFlag
1A25-Sep-20 
1A20-Sep-20 
2A2-Sep-20 
3B25-Sep-20 
3B20-Sep-20 
3B25-May-20 
4B20-May-20 
4B2-May-20 
4B2-Sep- 20 

 

Paul 

Kushal_Chawda

@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
Master
Master
Author

Hi Kush

Thank you very much. it work very well.