Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I load different versions of our data into Qlik each month. I want to return only the latest value. I've tried a number of methods but none seem to return what I want
Example
KPI Value Version
KPI1 100 1
KPI2 200 1
KPI3 300 1
KPI1 120 2
KPI3 320 2
KPI4 420 2
KPI3 330 3
KPI5 530 3
I want the above to return a value for each KPI but only the latest value
So, I want it to look like this:-
KPI Value Version
KPI1 120 2
KPI2 200 1
KPI3 330 3
KPI4 420 2
KPI5 530 3
I've tried FirstSortedValue (but that only returns the two values in Version 3) and a max(version) but again, it only returns the values from 1 version.
I hope this all makes sense, does anyone have any suggestions?
Thanks.
Hi Karl,
Use this Script
Tab1:
Load * Inline [
KPI,Value,Version
KPI1,100,1
KPI2,200,1
KPI3,300,1
KPI1,120,2
KPI3,320,2
KPI4,420,2
KPI3,330,3
KPI5,530,3 ];
Right join
Tab2:
Load KPI,
Max(Version) as Version
Resident Tab1
Group By KPI;
Output will be like this
Regards,
Vivek
Use Group By:
Load KPI,
Max(Value) AS Value
Resident Table Group By KPI;
DROP Table Table;
See the Attachment.
Regards
Aviral Nag
Dear Karl Hart,
kindly find the attached file.
i hope it will help you,
Thanks,
Mukram.
Thanks guys but we are having problems getting our licences sorted so unable to open any external files at the moment. Is there any way you could post the code directly onto here?
Thanks
Ok.
Than use the script which I have shared. Kindly Group By Value by KPI
Load KPI,
Max(Value) AS Value
Resident Your Table Name Group By KPI;
DROP Table Your Table Name;
//Script:
KPI_Table:
LOAD * Inline
[
KPI,Value,Version
KPI1,100,1
KPI2,200,1
KPI3,300,1
KPI1,120,2
KPI3,320,2
KPI4,420,2
KPI3,330,3
KPI5,530,3
];
STORE KPI_Table into KPI_Table.qvd(qvd);
One:
LOAD KPI,Max(Value) as Max_Value
Resident KPI_Table
Group by KPI;
DROP Table KPI_Table;
Left Join
Two:
LOAD KPI,
Value,
Version
FROM
(qvd)
where Exists(Max_Value,Value);
Thanks,
Mukram.
FirstSortedValue should work. Did you include the GROUP BY KPI?
-Rob
Hi Karl,
Use this Script
Tab1:
Load * Inline [
KPI,Value,Version
KPI1,100,1
KPI2,200,1
KPI3,300,1
KPI1,120,2
KPI3,320,2
KPI4,420,2
KPI3,330,3
KPI5,530,3 ];
Right join
Tab2:
Load KPI,
Max(Version) as Version
Resident Tab1
Group By KPI;
Output will be like this
Regards,
Vivek
Rob, I've never used the Group By function, just trying to search on it now to see how to use it. Are you able to advise?
The RIGHT JOIN as Vivek suggested should work. But to demonstrate the FIrstSortedValue with GROUP BY:
Tab1:
LOAD
KPI,
FirstSortedValue(Value,Version) as Value,
max(Version) as Version
GROUP BY KPI
;
Load * Inline [
KPI,Value,Version
KPI1,100,1
KPI2,200,1
KPI3,300,1
KPI1,120,2
KPI3,320,2
KPI4,420,2
KPI3,330,3
KPI5,530,3 ];