3 Replies Latest reply: Aug 27, 2015 6:27 PM by Stefan Wühl RSS

    timestamps

    Lauris Graubins

      Hi there,

      would appreciate your advice.

       

      I have to calculate how long before flight departure time airport confirmes parking stand number for plane.

      Every flight has unique id number which contains lots of timestamps and other data.

       

      On most of occasions airport plans stands for arriving plane in avdance and then before plane lands, stand gets confirmed:

       

      0 stands for unconfirmed stand number,

      1 stands for confirmed stand number.

       

      STDLAST_UPDATEDCONFIRMED_STANDONSTAND
      7:25:003:13:52010
      7:25:006:23:4204
      7:25:006:24:2303
      7:25:006:38:4204
      7:25:006:38:5514
      7:25:006:50:2214
      7:25:006:58:3114
      7:25:007:04:5414
      7:25:007:06:2314
      7:25:007:17:5014
      7:25:007:21:2314
      7:25:007:26:0414
      7:25:007:28:2314
      7:25:007:58:1114

      So in this example  everything is straight forward:

      scheduled departure time is 7:25:00,

      stand number gets changed few times before it is confirmed,

      stand 4 gets confirmed at 6:38:55,

       

      I calculate time when stand gets confirmed as follows:

      STD - min({$<CONFIRMED_STAND = {"1"}>} LAST_UPDATED)

       

      Problem appears when on some occasions stand number gets confirmed then unconfirmed, then confirmed again:

        

      STDLAST_UPDATEDCONFIRMED_STANDONSTAND
      7:50:007:50:2007
      7:50:008:10:3503
      7:50:008:11:2913
      7:50:008:11:44010
      7:50:008:11:58110
      7:50:008:15:0808
      7:50:008:15:2018
      7:50:008:28:5518

       

      We can see that stand has been confirmed few times in given example, the truth is plane goes to stand which is confirmed as last in table, stand 8 gets confirmed at 08:15:20 which is 25min after departure time meaning that plane is arriving late.

      My STD - min({$<CONFIRMED_STAND = {"1"}>} LAST_UPDATED) does not work here as I don't want min value.

      The value I need is first timestamp which is greater than max timestamp when confirmed stand = 0.

       

      How do I calculate this in qlikview?

       

      Thank you,

      Lauris

        • Re: timestamps
          Stefan Wühl

          Attached a sample QVW with two approaches

           

          a) expression only:

          =Time(

          Min( Aggr( if( LAST_UPDATED > Max(TOTAL<STD> {<CONFIRMED_STAND = {0}>} LAST_UPDATED), LAST_UPDATED), STD, LAST_UPDATED))

          )

           

          (here I've added STD as aggr() dimension to be able to distinguish between different flights, use flight number if available instead)

           

          b) script based:

          You can flag the latest confirmed timestamp like

           

           

          LOAD *,

          if(CONFIRMED_STAND = 0, Previous(LAST_UPDATED)) as Latest_Confirmed

          RESIDENT INPUT

          ORDER BY LAST_UPDATED desc;

           

          Then find the max confirmed timestamp using max. (with a bit more effort, you can do also this in the script.

            • Re: timestamps
              Lauris Graubins

              Absolutely beautiful. Thanks a lot for this.

              I wish I was able to open your attachment, but as I have personal edition so I can’t.

              I’ve used expression which works fine. As advised I replaced STDs with ID number which is unique number for each flight.

              To get clear picture what expression does I wanted to ask, what does this bit of expression mean:

              TOTAL<STD>

              In my case I replaced it with ID number, does it describe search area of LAST_UPDATED within one flight?

              Once again thanks a lot,

              Lauris

                • Re: timestamps
                  Stefan Wühl

                  It tells QV to calculate MAX per STD across all LAST_UPDATED.

                   

                  Create a straight table chart with dimension STD and LAST_UPDATED and expression

                  Max(TOTAL<STD> {<CONFIRMED_STAND = {0}>} LAST_UPDATED), LAST_UPDATED)


                  to see what I mean.