Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Filling in data on upload

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.

3 Replies
adamdavi3s
Honored Contributor

Re: Filling in data on upload

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.

MVP
MVP

Re: Filling in data on upload

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;

Not applicable

Re: Filling in data on upload

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