10 Replies Latest reply: Sep 11, 2016 1:23 PM by Stefan Wühl RSS

    capture dates in between start date and end date

    Mohammed Rend

      Hello all,

       

      I tried looking for this but could not find a solution

       

      I have a table with the following table loaded into Qlikview

       

      Load

      [Employee Number]

      Absence Type

      [Date Start]

      [Date End]

       

       

      I now need to recreate the table so that it adds a date column which capture all the dates an employee took a leave

      Load

       

      [Employee Number]

      Absence Type

      Date

       

      Figured I need to loop in order to do this.  In programming world the logic would be

       

      load

      $vDifference = [Date End] - [Date Start]

      [Employee Number]

      Absence Type

       

      For i = 0, i < $vDifference, i++

       

      [Date Start] + i as Date;

       

       

      How do I do this in Qlikview?

        • capture dates in between start date and end date
          Stefan Wühl

          You can use a while statement in your load to achieve what you want, like

           

          SET DateFormat='DD.MM.YYYY';

           

          INPUT:

          Load * INLINE [

          Employee Number, Absence Type, Date Start, Date End

          100, Sick, 01.01.2012, 10.01.2012

          100, Sick, 15.01.2012, 21.01.2012

          ];

           

          RESULT:

          LOAD [Employee Number], [Absence Type],

          Date([Date Start]+iterno()-1) as Date

          resident INPUT while [Date Start]+IterNo()-1 <=[Date End];

           

          drop table INPUT;

           

          The INPUT table is just to load two sample lines. The important part is the while statement

          while [Date Start]+IterNo()-1 <=[Date End];

           

          and the use of the iterno() function in the statement that defines Date field

          Date([Date Start]+iterno()-1) as Date

           

          Hope this helps,

          Stefan

            • capture dates in between start date and end date
              Mohammed Rend

              thanks it worked .

               

               

              the logic doesnt make sense though, you mind explaining what interno() does ?

                • capture dates in between start date and end date

                  I was curius about IterNo() too (The name is not really self explaining).

                   

                  The reference guide (installs with QV, but in the "Programs/QV" folder) is a good source

                  (But the examples are rather short and simple. they are often useless for more delicate problems)

                   

                   

                  reference guide:

                  "IterNo( )

                  This function only has a meaning if used together with a while

                  clause (see the documentation on “Load” on page 317). IterNo( )

                  returns an integer indicating for which time one single record is

                  evaluated in a load statement with a while clause. The first iteration

                  has number 1."


                  So, IterNo writes in the new collumn "Date". It writes Dates between [Date Start] and [Date End].

                  It does so, by writing Datens higher as [Date Start] until [Date End] is reached.

                   

                  Do i need to mention, that the example in the reference guide is simplified too much to be any use for this (or any other problems besides writing some integers). There is an example, but its in the example secion of LOAD (and only to be found by full text search).

                    • capture dates in between start date and end date
                      Stefan Wühl

                      I think the reference guide is often a bit short on explaining, sometimes unclear or even wrong. In this case, I personally find the explanation sufficient, could be because I do have a programming background so I may implicitely add information that others may miss here.

                       

                      You need to look first at the while statement (from the Help):

                      while is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is true. In order to be useful, a while clause must typically include the IterNo( ) function.

                       

                      You need to make yourself clear that there is an input table (coming from your source, in this example it's the INLINE table) and the output table (going into the QV data model). Your load is transforming this input table into an output table, in simple settings, it's just piping the exact same number of records, fields through, like a LOAD * from Table;

                       

                      The while clause will create multiple output records from one input record, using the while condition to decide when to stop. And the only thing that changes between creating two output records from one input record is the iteration no, which you get by calling the iterno() function. In e.g. C programming language, you would create a counter variable and explicitely increment this variable in each loop.

                       

                      So, this should explain

                      while [Date Start]+IterNo()-1 <=[Date End];

                       

                      --> Create one output record for each date between Date Start and Date End (The calculation is possible since QV dates have a numerical representation, the integer part is counting day since a fixed starting point in time, like Excel or other systems do also, for example 01.01.2012 has a numerical representation of 40909, 10.01.2012 of 40918). I need to subtract 1 since iterno() starts with 1 for the first created output record, but I want to get at least one record if Date Start and Date End are the same.

                       

                      If this is all I do, I would create just duplicate output records created from my input record. So I can use the iterno() another time to actually change each output record a bit

                       

                      Date([Date Start]+iterno()-1) as Date

                       

                      Create a new Date value, starting from the Date Start read from the input table, adding one day per loop increment (again, I need to take care of the iterno() starting from 1 since I want also the Date Start to be included).

                       

                      If you compare the two lines of my input INLINE table with the output table after the executed load, it should make everything clear.

                       

                      Hope this helps,

                      Stefan