Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

hmmdunno
New Contributor

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

Greetings,

i have data that looks like this:

Report MonthGate 1Gate 2Gate 3Gate 4Gate 5Gate 6Gate 7Gate 8
Jan13.05.201603.03.201703.03.201729.09.201727.10.201730.03.201829.12.201814.06.2019
Feb13.05.201603.03.201703.03.201730.06.201731.07.201730.03.201829.12.201814.06.2019
Mär13.05.201603.03.201731.05.201730.06.201731.07.201730.03.201829.12.201814.06.2019
Apr15.06.201631.05.201730.06.201731.12.201715.02.201830.03.201829.12.201814.06.2019
Mai30.06.201631.05.201730.06.201731.12.201715.02.201830.03.201829.12.201814.06.2019
Jun01.06.201630.04.201730.04.201731.12.201715.02.201830.03.201829.12.201814.06.2019
Jul 30.04.201730.06.201731.12.201715.02.201830.03.201829.12.201814.06.2019
Aug 31.08.201731.12.201715.02.201830.03.201829.12.201814.06.2019
Sep 31.08.201731.12.201715.02.201831.08.201829.12.201814.06.2019
Okt15.02.201831.05.201831.08.201829.12.201814.06.2019
Nov 15.02.201831.05.201831.08.201830.04.201914.06.2019
Dez 31.05.201831.08.201830.04.201914.06.2019

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

  

GatesLatest Date
101.06.2016
230.04.2017
331.08.2017
415.02.2018
531.05.2018
631.08.2018
730.04.2019
814.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?

1 Solution

Accepted Solutions
MVP
MVP

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

May be this in that case

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

6 Replies
MVP
MVP

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

May be this

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

hmmdunno
New Contributor

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

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.

MVP
MVP

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

May be this in that case

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

kaanerisen
Contributor III

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

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)

Untitled.png

hmmdunno
New Contributor

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

Works like a charm. Thank you a lot!

hmmdunno
New Contributor

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

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!