Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
//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')
I tried the approach , but it giving me the data like this :
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
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,
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;