Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aspiring_Developer
Creator III
Creator III

Load field having the most recent date in qlik

Hello everyone.

Please help me as I am new to qlik.

Below is the data 

Milestone Date

M1.  202001

M1 ,201901

M2, 202003

M2, 202006

Desired output 

M1 202001

M2 202006

Want to capture milestone for the latest date 

Please help 

@reddy-s  @kumar1  @Kushal_Chawda 

1 Solution

Accepted Solutions
Kushal_Chawda

try below

Data:
load Milestone,
     date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Source;

inner join(Data)
load date(max(Date),'YYYYMM') as Date
resident Data;

View solution in original post

4 Replies
Kushal_Chawda

//Script logic

Data:
load Milestone,
     date(max(floor(date#(Date,'YYYYMM'))),'YYYYMM') as Date
FROM Source
group by Milestone;

// Front end solution

Create a table object with Dimension Milestone and below expression

Date(max(Date),'YYYYMM')

Aspiring_Developer
Creator III
Creator III
Author

@Kushal_Chawda

I tried the approach , but it giving me the data like this :

 

Aspiring_Developer_0-1598592109154.png

From backend itself , i should only get the latest values for Milestone ie.

if i take this single field in table it should  populate values only for latest dates.

M1

M2

M3

 

M1, M2,M3 having values for latest date

SerhanKaraer
Creator III
Creator III

Hello Aspiring_Creator,

As @Kushal_Chawda suggested, getting max for each milestone should generate the output you need.

Likewise, you can use sorting function and get the same result.

LOAD MileStone, FirstSortedValue(Date,-Date) as Date GROUP BY MileStone;
LOAD * INLINE [
MileStone, Date
M1, 202001
M1, 201901
M2, 202003
M2, 202006
];

 

Regards,

Kushal_Chawda

try below

Data:
load Milestone,
     date(date#(Date,'YYYYMM'),'YYYYMM') as Date
FROM Source;

inner join(Data)
load date(max(Date),'YYYYMM') as Date
resident Data;