topic max per key in script in QlikView

max per key in script

Mon, 02 May 2011 12:44:13 GMT

Hi,

I have the following table:

The fiels "SLEUTEL" is my key field.

As you can see, some key-fields van have multiple start-dates (DATUM_VAN_WAARDE). (the red square on the image)

I'm looking for a way to load only the most recent start-date per key-field.

So I could express it like DATUM_VAN_WAARDE = aggr((max(DATUM_VAN_WAARDE),SLEUTEL).

Off course my records have a lot of others fields that i'm not showing in this image 🙂

Anyone knows how I can do this in my script?

Thanks!

AW:max per key in script

brenner_martina — Mon, 02 May 2011 12:55:16 GMT

Hi,

you can use Max() and Group By in script:

Load SLEUTEL,
Max(DATUM_VAN_WAARDE) As MaxDatum
From Table Group By SLEUTEL;

SV:max per key in script

ToniKautto — Mon, 02 May 2011 12:57:07 GMT

Please clarify in more detail what you are trying to achieve. Are you looking for a way to load the max values only in the load script? Or are you looking for a way to get only the max values presented in a table in your QV app?

max per key in script

Mon, 02 May 2011 13:19:05 GMT

A bit more info.

In my table i have reference-values.

So for example 10 years ago, this value was 50.

But then the standards changed an the reference values needed to change too.

So for example this changes to 80.

Now in my table i have all reference values, and i only want these that are now relevant for me (the most recent).

I'va added the value-field to my table.

If you check key 2645184, in 1993 the value for this key was 180, but in 1995 this changed to 41.

So i would like to load only the most recent data per key.

max per key in script

Anonymous — Mon, 02 May 2011 13:27:43 GMT

Is Martina's answer not working for you? :

you can use Max() and Group By in script:

Load SLEUTEL,
Max(DATUM_VAN_WAARDE) As MaxDatum
From Table Group By SLEUTEL;

AW:Re: max per key in script

brenner_martina — Mon, 02 May 2011 13:27:43 GMT

Hi,

use this script and you get always only the latest WAARDE!

Load SLEUTEL,
Only(WAARDE) As ActualWAARDE,
Max(DATUM_VAN_WAARDE) As MaxDatum
From Table Group By SLEUTEL;