topic Re: Show Most Up-to-date Value in QlikView

Show Most Up-to-date Value

Karl_Hart — Tue, 22 Jul 2014 10:37:26 GMT

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.

Re: Show Most Up-to-date Value

aveeeeeee7en — Tue, 22 Jul 2014 10:55:43 GMT

Use Group By:

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

See the Attachment.

Regards

Aviral Nag

Re: Show Most Up-to-date Value

mdmukramali — Tue, 22 Jul 2014 11:09:48 GMT

Dear Karl Hart,

kindly find the attached file.

i hope it will help you,

Thanks,

Mukram.

Re: Show Most Up-to-date Value

Karl_Hart — Tue, 22 Jul 2014 11:14:10 GMT

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

Re: Show Most Up-to-date Value

aveeeeeee7en — Tue, 22 Jul 2014 11:17:48 GMT

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;

Re: Show Most Up-to-date Value

mdmukramali — Tue, 22 Jul 2014 11:18:15 GMT

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

Re: Show Most Up-to-date Value

rwunderlich — Tue, 22 Jul 2014 11:38:52 GMT

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

-Rob

Re: Show Most Up-to-date Value

Tue, 22 Jul 2014 11:43:49 GMT

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

Re: Show Most Up-to-date Value

Karl_Hart — Tue, 22 Jul 2014 11:44:04 GMT

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?

Re: Show Most Up-to-date Value

rwunderlich — Tue, 22 Jul 2014 11:54:40 GMT

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