Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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?

View solution in original post

9 Replies
Not applicable
Author

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

Not applicable
Author

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

Appreciated for help

Not applicable
Author

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
Author

Thanks for reply.

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

hic
Former Employee
Former Employee

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
Author

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
Author

Thanks HIC.

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

hic
Former Employee
Former Employee

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
Author

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?