Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Populate the Missing Data

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.

1 Solution

Accepted Solutions
Not applicable

Re: Populate the Missing Data

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?

9 Replies
Not applicable

Re: Populate the Missing Data

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

Not applicable

Re: Populate the Missing Data

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

Appreciated for help

Not applicable

Re: Populate the Missing Data

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

Not applicable

Re: Populate the Missing Data

Thanks for reply.

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

Employee
Employee

Re: Populate the Missing Data

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

Not applicable

Re: Populate the Missing Data

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.

Not applicable

Re: Populate the Missing Data

Thanks HIC.

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

Employee
Employee

Re: Populate the Missing Data

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

or

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

     ...

or

Load *

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

Load

     11 + RecNo() as year,

     IterNo() as month,

     ...

     While IterNo() <= 12

HIC

Not applicable

Re: Populate the Missing Data

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?

Community Browser