topic Re: Last value in QlikView

Last value

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

How can I get the last value per dimension in a straight table . so for

LOAD * INLINE [

Name, Score

A,10

B,2

C,30

A,15

B,5

D,70

];

expecting to get

Name Score

A 15

B 2

C 30

D 70

Thank you,

Raphael

Re: Last value

sunny_talwar — Thu, 25 Jan 2018 09:37:13 GMT

You took the first value for B and Second value for A.... what is the logic here or do you want to show 5 for B?

Re: Last value

sunny_talwar — Thu, 25 Jan 2018 09:38:06 GMT

Without intervention in the script, you can try this

Aggr(If(RowNo() = NoOfRows(), Score), Name, Score)

Re: Last value

sunny_talwar — Thu, 25 Jan 2018 09:39:56 GMT

With intervention in the script

LOAD RowNo() as Key,

LOAD * INLINE [

Name, Score

A,10

B,5

C,30

A,15

B,2

D,70

];

Create a new Key field and use it like this

FirstSortedValue(Score, -Key)

Re: Last value

Anonymous — Thu, 25 Jan 2018 09:42:09 GMT

Many Thanks Sunny, as always you are saving the day!

Re: Last value

isingh30 — Sat, 27 Jan 2018 17:19:12 GMT

Another way of doing this.

LOAD * INLINE [

Name, Score

A,10

B,5

C,30

A,15

B,2

D,70

]where Score <> 10 and Score <> 5;

Thanks.

Re: Last value

shiveshsingh — Sun, 28 Jan 2018 02:56:07 GMT

A:LOAD * INLINE [

Name, Score

A,10

B,2

C,30

A,15

B,5

D,70

];

inner join

load max(Score) as Score,Name

Resident A

group by Name;