4 Replies Latest reply: Dec 8, 2017 8:02 AM by kaan erisen RSS

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

    Oleg Klein

      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?