8 Replies Latest reply: Oct 8, 2017 11:38 AM by beck bakytbek

# LastValue ()

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:

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

• ###### Re: LastValue ()

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

TEST:

TARIF,

lastvalue(ANSATZ) as ANSATZ,

lastvalue(DATUM) as DATUM

Resident TEST1 group by TARIF;

• ###### Re: LastValue ()

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?

• ###### Re: LastValue ()

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

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)

*

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

• ###### Re: LastValue ()

Hi Riccardo,

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

Beck

• ###### Re: LastValue ()

Hi Beck,

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

• ###### Re: LastValue ()

My Field: DATUM is a Number

• ###### Re: LastValue ()

Another option can be:

TEST:

TARIF, DATUM, ANSATZ

5, 07.03.2011, 100

5, 01.03.2009, 70

];

Inner Join (TEST)

Max(DATUM) as DATUM

Resident TEST

Group By TARIF;

• ###### Re: LastValue ()

Hi Martin,

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

Thanks a lot

Beck