max per key in script

[#1]

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!

[brenner_martina]

Hi,

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

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

[ToniKautto]

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?

[#4]

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.

[Anonymous]

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;

[brenner_martina]

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;