topic Re: Populate the Missing Data in QlikView

Populate the Missing Data

Tue, 11 Jun 2013 21:18:50 GMT

Hi Folks, I am trying to populate the missing data. Please find the attched qvw and qvd files for reference.

I have the 3 fields in QVD: ID, MonthName, Qunatity

Some of the ID's dont have the Quantity values for particular MonthNames. So I need to populate the Missing quantity values with Zero for Each ID & MonthName

Please provide the suggestions.

Re: Populate the Missing Data

Tue, 11 Jun 2013 21:37:11 GMT

Right click on your chart > Properties > Dimensions > for each dimension check the box Show All Values.

Re: Populate the Missing Data

Tue, 11 Jun 2013 21:55:12 GMT

Thanks for reply. Is there any way to do Script level?

Appreciated for help

Re: Populate the Missing Data

Tue, 11 Jun 2013 21:59:23 GMT

From Gysbert Wassenaar:

You can also replace nulls with 0's in the script by adding these two lines at the top of the script:

NullAsValue *;

set NullValue=0;

http://community.qlik.com/message/350235#350235

Re: Populate the Missing Data

Wed, 12 Jun 2013 11:40:35 GMT

Thanks for reply.

I am looking for a logic to create the Null values for Missing data.

Re: Populate the Missing Data

hic — Wed, 12 Jun 2013 11:52:08 GMT

You should do something similar to what I describe in http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field.

The difference, however, is that your problem is two-dimensional (ID+MonthName) and the blog post is a bout a one-dimensional case. The document on http://community.qlik.com/docs/DOC-3786 has an example for a two-dimensional case.

HIC

Re: Populate the Missing Data

Wed, 12 Jun 2013 12:04:36 GMT

I checked the "Show All values" on the Dimesions tab. Still I didn't see all the values in the chart.

Can you please provide qvw.

Re: Populate the Missing Data

Wed, 12 Jun 2013 12:25:55 GMT

Thanks HIC.

I saw your blog. but in my case MonthName is string. How can I loop over the string value ?

Re: Populate the Missing Data

hic — Wed, 12 Jun 2013 12:43:41 GMT

For each vMonth in '01_13','02_13','03_13','04_13','05_13','06_12','07_12','08_12','09_12','10_12','11_12','12_12'

...

Next vMonth

Load

subfield('01_13,02_13,03_13,04_13,05_13,06_12,07_12,08_12,09_12,10_12,11_12,12_12',',') as Month

...

Load *

Num(month,'00') & '_' & Num(year,'00') as Month;

Load

11 + RecNo() as year,

IterNo() as month,

...

While IterNo() <= 12

HIC

Re: Populate the Missing Data

Wed, 12 Jun 2013 13:36:04 GMT

Thanks HIC. I can't hard code MonthName Values because these values got changed on time varies.

I work around and finally done it. Please find the below script:

TEMP:

LOAD DISTINCT MONTHNAME FROM T_12M.qvd (qvd);

LET vCount = NoOfRows('TEMP');

MAPMN:

MAPPING LOAD MONTHNAME, ROWNO() AS VALUE Resident TEMP;

MNMAP:

MAPPING LOAD ROWNO() AS VAL, MONTHNAME AS MNA RESIDENT TEMP;

DROP Table TEMP;

T1:

LOAD DISTINCT ORG_INVITEM_ID FROM T_12M.qvd (qvd);

TEMPDATA:

LOAD ORG_INVITEM_ID ,IterNo() AS MONTHNO Resident T1 While IterNo() <= $(vCount);

Left Join

LOAD ORG_INVITEM_ID , APPLYMAP('MAPMN',MONTHNAME) AS MONTHNO , QUANTITY FROM T_12M.qvd (qvd);

DROP Table T1;

NULLMAP:

MAPPING LOAD NULL(), 0 AutoGenerate 1;

MAP QUANTITY using NULLMAP;

DATA:

NoConcatenate

LOAD ORG_INVITEM_ID,MONTHNO, ApplyMap('MNMAP',MONTHNO) AS MONTHNAME, QUANTITY Resident TEMPDATA;

DROP Table TEMPDATA;

UNMAP;

I am working on large dataset and I am worried about the performance. Can you please provide the comments on my script?