topic Re: Filling in data on upload in QlikView

Filling in data on upload

Tue, 16 Aug 2016 07:15:58 GMT

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

adamdavi3s — Tue, 16 Aug 2016 07:40:08 GMT

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

maxgro — Tue, 16 Aug 2016 08:25:50 GMT

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;

Re: Filling in data on upload

Wed, 17 Aug 2016 07:08:53 GMT

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