4 Replies Latest reply: Nov 10, 2012 12:07 PM by steve peroni RSS

    problem with table structure

    steve peroni


      I've the following table:


      Company code          Start Validity Date     End Validity Date

      ABC123                         01/01/2007               31/12/2200


      I need to produce a table like this:


      Company code      Start Validity Date End Validity Date

      ABC123                    01/01/2007               31/12/2007

      ABC123                    01/01/2008               31/12/2008

      ABC123                    01/01/2009               31/12/2009

      ABC123                    01/01/2010               31/12/2010




      How can I produce the table above ? Is there a function in qlikview to build this structure ?


      Thanks in advance for your answers

        • Re: problem with table structure
          Stefan Wühl

          You should be able to generate your records using a WHILE loop in your script, in a simple setting (start and end date at the beginning and end of a year), it could look like:



          LOAD * INLINE [

          Company code,          Start Validity Date,     End Validity Date

          ABC123,                         01/01/2007,               31/12/2200




          NoConcatenate LOAD

          [Company code],

          Date(AddYears([Start Validity Date], iterno()-1)) as [Start Validity Date],

          Date(daystart(YearEnd(AddYears([Start Validity Date],iterno()-1)))) as [End Validity Date]

          Resident INPUT

          while AddYears([Start Validity Date],iterno()-1) <= [End Validity Date];



          drop table INPUT;

            • Re: problem with table structure
              steve peroni

              Hi Swuehl,

              thanks very much, you gave me a very big help.

              Just another thing: if you could explain me how it works , because it's a bit dark for me


              Thanks again for you time and help




                • Re: problem with table structure
                  Stefan Wühl

                  By default, a LOAD statement creates one record in the output table (the table that is created in the QV data model) from one record read from the input table / source.


                  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."


                  So you are creating multiple records from the same input record, for each output record, iterno() will be increased by one and you get record as long as (in your case)


                  AddYears([Start Validity Date],iterno()-1) <= [End Validity Date]


                  Hope this helps,