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: 
Karl_Hart
Creator
Creator

Show Most Up-to-date Value

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.

1 Solution

Accepted Solutions
Not applicable

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

KPI.png

Regards,

Vivek

View solution in original post

9 Replies
aveeeeeee7en
Specialist III
Specialist III

Use  Group By:

Load KPI,
Max(Value) AS Value
Resident Table Group By KPI;
DROP Table Table;

See the Attachment.

Regards

Aviral Nag

mdmukramali
Specialist III
Specialist III

Dear Karl Hart,

kindly find the attached file.

i hope it will help you,

Thanks,

Mukram.

Karl_Hart
Creator
Creator
Author

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

aveeeeeee7en
Specialist III
Specialist III

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;

mdmukramali
Specialist III
Specialist III

//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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

FirstSortedValue should work. Did you include the GROUP BY KPI?

-Rob

Not applicable

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

KPI.png

Regards,

Vivek

Karl_Hart
Creator
Creator
Author

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?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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