topic Re: Case 945 How to pick the latest date for each Doc_Num ? in QlikView

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

paulyeo11 — Fri, 02 Oct 2020 22:07:14 GMT

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

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

Vegar — Fri, 02 Oct 2020 22:50:25 GMT

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;

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

paulyeo11 — Fri, 02 Oct 2020 22:36:30 GMT

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

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

Vegar — Fri, 02 Oct 2020 22:49:50 GMT

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

Left join LOAD Doc_num...

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

paulyeo11 — Fri, 02 Oct 2020 22:55:54 GMT

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

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

Kushal_Chawda — Sat, 03 Oct 2020 10:36:51 GMT

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

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

paulyeo11 — Sun, 04 Oct 2020 05:13:04 GMT

Hi Kush

Thank you very much. it work very well.