topic Re: How to get the last field value that isn't NULL in App Development

How to get the last field value that isn't NULL

Anonymous — Wed, 06 Dec 2017 14:41:29 GMT

Greetings,

i have data that looks like this:

Report Month	Gate 1	Gate 2	Gate 3	Gate 4	Gate 5	Gate 6	Gate 7	Gate 8
Jan	13.05.2016	03.03.2017	03.03.2017	29.09.2017	27.10.2017	30.03.2018	29.12.2018	14.06.2019
Feb	13.05.2016	03.03.2017	03.03.2017	30.06.2017	31.07.2017	30.03.2018	29.12.2018	14.06.2019
Mär	13.05.2016	03.03.2017	31.05.2017	30.06.2017	31.07.2017	30.03.2018	29.12.2018	14.06.2019
Apr	15.06.2016	31.05.2017	30.06.2017	31.12.2017	15.02.2018	30.03.2018	29.12.2018	14.06.2019
Mai	30.06.2016	31.05.2017	30.06.2017	31.12.2017	15.02.2018	30.03.2018	29.12.2018	14.06.2019
Jun	01.06.2016	30.04.2017	30.04.2017	31.12.2017	15.02.2018	30.03.2018	29.12.2018	14.06.2019
Jul		30.04.2017	30.06.2017	31.12.2017	15.02.2018	30.03.2018	29.12.2018	14.06.2019
Aug			31.08.2017	31.12.2017	15.02.2018	30.03.2018	29.12.2018	14.06.2019
Sep			31.08.2017	31.12.2017	15.02.2018	31.08.2018	29.12.2018	14.06.2019
Okt				15.02.2018	31.05.2018	31.08.2018	29.12.2018	14.06.2019
Nov				15.02.2018	31.05.2018	31.08.2018	30.04.2019	14.06.2019
Dez					31.05.2018	31.08.2018	30.04.2019	14.06.2019

And I want to make a table that shows the last given date for each "Gate" that is not NULL, like this:

Gates	Latest Date
1	01.06.2016
2	30.04.2017
3	31.08.2017
4	15.02.2018
5	31.05.2018
6	31.08.2018
7	30.04.2019
8	14.06.2019

To get the Gates into Rows, I used ValueList(...). With that i planned on creating custom measures for each Gate by using if-statements (since they're in different Fields in the Source Data), like:

if(ValueList(...)='1'

,Get latest date from Field "Gate 1"

,if(ValueList(...)='2'

,Get latest date from Field "Gate 2"

,if(ValueList(...)='3'

and so on...

But how do I get the latest value from each field? Max([Gate X]) doesn't work, because newer dates may be earlier than the ones before (see "Gate 1").

I created a calculated Field that translates the month names into numbers 1 to 12, so that i could use FirstSortedValue() in a For...Next-Statement to just check every row with isnull() counting down from 12, but For...Next is not available as a chart function in Qlik Sense.

So next i tried checking every row via chained if-statements, but the script won't allow the necessary amount of characters for a single measure (would have been 11 IFs for each of the 8 Gates).

So my question is: What function could i put in place of "Get latest date from Field 'Gate X'" for it to work?

Re: How to get the last field value that isn't NULL

sunny_talwar — Wed, 06 Dec 2017 16:24:19 GMT

May be this

Max({<[Gate X] = {"*"}>}[Gate X])

Re: How to get the last field value that isn't NULL

Anonymous — Fri, 08 Dec 2017 07:42:29 GMT

Thank you for the reply, but that doesn't do it unfortunately.

As stated, Max() doesn't work because Gate-Dates given in later Report Months may be earlier than the ones before. For "Gate 1" your suggestion returns the highest value (30.06.2016 from May) instead of the desired 01.06.2016 from June.

I had the idea of using count([Gate X]) as a rank modifier for FirstSortedValue(), i.e.

FirstSortedValue([Gate X], [Report Month as number], count([Gate X]),

so i would always get the date in the lowest filled row. But FirstSortedValue() doesn't seem to accept functions as a rank. Just typing the correct number works, but using count() only returns NULL.

Re: How to get the last field value that isn't NULL

sunny_talwar — Fri, 08 Dec 2017 12:14:55 GMT

May be this in that case

FirstSortedValue({<[Gate X] = {"*"}>} [Gate X], -[Report Month as number])

Re: How to get the last field value that isn't NULL

kaanerisen — Fri, 08 Dec 2017 13:02:05 GMT

Hi,

I am not sure about your data model but it will work for you I guess.

Exp: FirstSortedValue({<date={"=len(date)>0"}>} date, -RM_Num)

Re: How to get the last field value that isn't NULL

Anonymous — Wed, 13 Dec 2017 08:48:27 GMT

Works like a charm. Thank you a lot!

Re: How to get the last field value that isn't NULL

Anonymous — Wed, 13 Dec 2017 08:55:35 GMT

The Table I posted originally is what the raw data looks like.

Which means this doesn't work for my particular case, but your solution would be correct if the raw data looked like in your picture. But still, thanks a lot! Because I do have other data that is very similar to what you posted and your function will help me with that. It's like you looked into the future and solved a problem i didn't know i had yet!