Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
Right click on your chart > Properties > Dimensions > for each dimension check the box Show All Values.
Thanks for reply. Is there any way to do Script level?
Appreciated for help
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;
Thanks for reply.
I am looking for a logic to create the Null values for 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
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.
Thanks HIC.
I saw your blog. but in my case MonthName is string. How can I loop over the string value ?
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
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?