LastValue ()

[beck_bakytbek]

Hi Folks,

i got a situation, my table does look like:

TARIF, DATUM, ANSATZ

5, 07.03.2011, 100

5, 01.03.2009, 70

my expected output  only is:

5, 07.03.2011, 100

in th script area i am using this code:

TEST:

Load

TARIF,

ANSATZ,

lastvalue(DATUM) as DATUM

Resident TEST1 group by TARIF,ANSATZ;

after reload i am still having:

TARIF, DATUM, ANSATZ

5, 07.03.2011, 100

5, 01.03.2009, 70

how to solve this situation, to have only this result:

5, 07.03.2011, 100

does anybody have any idea? any help and feedback are appreciated

Beck

[rubenmarin]

Hi Beck, grouping by TARIF and ANSATZ will return two rows because there are 2 different combinations in those values, maybe?:

TEST:

Load

TARIF,

lastvalue(ANSATZ) as ANSATZ,

lastvalue(DATUM) as DATUM

Resident TEST1 group by TARIF;

[beck_bakytbek]

Hi Ruben,

first of all, thanks a lot for your feedback,

i tried above code, but i recieve: 5, 07.03.2011, 70

my expected output is: 5 , 07.03.2011, 100

do you have any idea?

[rzenere_avvale]

Hi Beck,

I think the problem here is in the function you're trying to use. From LastValue  - script function ‒ Qlik Sense‌, this function returns the last value inserted for a group by. The row you're trying to obtain is the first, and with that granularity I think there's no way to obtain only that.

So I would try something like this:

//Determine the max DATUM in the dataset, for each TARIF

TMP:

Noconcatenate

LOAD

     ANSATZ,

     max(DATUM) as DATUM

Resident

     TEST1

group by

     ANSATZ;

//Keep only the rows that match the data in the TMP table

inner join (TEST1)

LOAD

     *

Resident

     TMP;

//Drop the temporary table

drop table TMP;

Personally I don't like much the LastValue() function, because most of the times there are reviews in the dataset and rows from older dates are inserted.

Let me know if this helps.

Cheers,

Riccardo

[beck_bakytbek]

Hi Riccardo,

Thanks a lot for your feedback, but it does not work

Beck

[rzenere_avvale]

Hi Beck,

the DATUM field is a number or a string? Max() works with numbers...

[beck_bakytbek]

My Field: DATUM is a Number

[rubenmarin · Accepted solution]

Another option can be:

TEST:

LOAD * Inline [

TARIF, DATUM, ANSATZ

5, 07.03.2011, 100

5, 01.03.2009, 70

];

Inner Join (TEST)

LOAD TARIF,

Max(DATUM) as DATUM

Resident TEST

Group By TARIF;

[beck_bakytbek]

Hi Martin,

Thanks a lot for your helps and feedback, i used this solution and it does work.

Thanks a lot

Beck