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.

       

      Example

      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

              Veronica

            • Re: Filling in data on upload
              Massimo Grossi

              try this


               

              Input:

              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

              load

                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);

               

                CurrentMonth:

                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

                ELSE

                    trace *** > 0 rows in CurrentMonth;

                    IF NoOfRows('LastMonthWithData') >=0 THEN

                          DROP TABLE LastMonthWithData;

                    ENDIF

                    LastMonthWithData:

                    NoConcatenate LOAD * Resident CurrentMonth;

                ENDIF

               

                DROP Table CurrentMonth;

               

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

               

              LOOP

               

              DROP Table LastMonthWithData;