3 Replies Latest reply: Aug 17, 2016 3:08 AM by Veronica Odayar RSS

    Filling in data on upload

    Veronica Odayar

      Good day


      I am uploading data into Qlikview however the datasource does not have data for certain months.  I would like to duplicate the last months entry as a duplicate for the missing month end dates.



      If the datasource has the following entries


      31/01/2016     Mary

      30/04/2016      John


      I would like the file to duplicate the entry for January 2016 for February 2016 and March 2016 ie. create entries for the missing months with the last available months data.


      Thank you for your assistance.

        • Re: Filling in data on upload
          Adam Davies

          There is an excellent thread on this here


          Generating Missing Data In QlikView




          Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

            • Re: Filling in data on upload
              Veronica Odayar

              Good day Adam


              Thank you for the response to my enquiry.  This was very helpful in terms of creating a calendar.


              However , I would like to only create month end dates in the master calendar.  In addition with regards to the peek function, how do I incorporate peeking of multiple fields from the previous dates entry for missing data.  Where in the script would I incorporate the peedk function.


              Thanks and regards


            • Re: Filling in data on upload
              Massimo Grossi

              try this



              load * inline [

              Date, User

              31/01/2016 ,    Mary

              31/01/2016 ,    Max

              30/04/2016 ,    John

              30/04/2016 ,    Max

              30/04/2016 ,    Sam

              31/08/2016 ,    John



              // get min max month


                min(Date) as MinMonth,

                max(Date) as MaxMonth

              Resident Input;


              LET vMonth = peek('MinMonth');

              LET vMaxMonth = peek('MaxMonth');

              trace vMonth=$(vMonth);

              trace vMaxMonth=$(vMaxMonth);


              // month loop

              DO while vMonth <= vMaxMonth


                LET vMonth2 = Date($(vMonth));

                trace; trace ***** vMonth=$(vMonth) vMonth2=$(vMonth2);



                NoConcatenate LOAD *

                Resident Input

                Where Date = '$(vMonth2)';


                // current month missing, add rows from last month with data

                IF NoOfRows('CurrentMonth') = 0 THEN

                    trace *** 0 rows in CurrentMonth;

                    Concatenate (Input)

                    LOAD date(Floor(MonthEnd($(vMonth)))) as Date, User

                    Resident LastMonthWithData;

                // current month with rows, save in last month with data


                    trace *** > 0 rows in CurrentMonth;

                    IF NoOfRows('LastMonthWithData') >=0 THEN

                          DROP TABLE LastMonthWithData;



                    NoConcatenate LOAD * Resident CurrentMonth;



                DROP Table CurrentMonth;


                Let vMonth = Floor(MonthEnd(AddMonths(vMonth, 1)));




              DROP Table LastMonthWithData;