topic How to get the last value for a specific field with null among values in QlikView

How to get the last value for a specific field with null among values

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

Dear all,

I'm trying to get a specific value in table bellow:

Date	Value
31-dez-18	3,87480
31-jan-19	4,25770
28-fev-19	3,65365
31-mar-19	3,74815
30-abr-19	4,15305
31-mai-19	3,93085
30-jun-19	3,99555
31-jul-19	4,15670
31-ago-19	3,79070
30-set-19	3,81860
31-out-19	4,21535
30-nov-19	4,03070
31-dez-19	3,87460
31-jan-20	4,25770
29-fev-20	3,99999
31-mar-20
30-abr-20
31-mai-20
30-jun-20
31-jul-20
31-ago-20
30-set-20
31-out-20
30-nov-20
31-dez-20

I'm looking for the last one in Value field => '3,99999'!

I tried to use this function: =FirstSortedValue(Value, -Value)
But the value that is returning to me is '-'

I'll use this in a Text Object...
Is just to show the value!

Could you guys help me?

Re: How to get the last value for a specific field with null among values

Sergey_Shuklin — Thu, 05 Mar 2020 11:35:55 GMT

Hello!

Where do you want to get it? On script load or on a front-end side?

Re: How to get the last value for a specific field with null among values

FabioP — Thu, 05 Mar 2020 11:39:22 GMT

I'm using a Text Object...
Is just to show the value!

Re: How to get the last value for a specific field with null among values

tresB — Thu, 05 Mar 2020 11:57:07 GMT

Try like:

=FirstSortedValue( {<Value={"=len(trim(Value))>0"}>}Value, -Date#(Date,'DD-MMM-YY'))

Re: How to get the last value for a specific field with null among values

Sergey_Shuklin — Thu, 05 Mar 2020 11:58:16 GMT

You can use this method:

=max({<Date={"$(=date(max({<Value={'>0'}>}Date)))"}>}Value)

First max() function - for cases when you have multiple values in output (You can use smth else like min() or only())

Second max in set analysis - for checking which date we should pick (value should be greater than zero)

And one more thing: make sure your Date field has the same format as the output of set analysis [date(max({<Value={'>0'}>}Date))]