Load missing fields as 0

[#1]

Dear all,

I have spent quite a lot of time with the following issue, but I'm still not able to get a result which I would need. I have data which are preloaded via SQL load to the qvd file. Once I load data to the qvd file I need to load data to my QV report and work with these data here.

Data structure - Source QVD file contains Week, Item number and Quantity. Basically these data present consumption of item for each week in past 52 weeks. The problem is that there is no record for combination week and item no. if there was no consumption in the week. If I load all the data as they are in the QVD, I can suppress missing values and display them as 0, but I cannot count(AVG, STDEV, etc) with these 0 characters, because it just a visual substitution.

So my question is if there is any easy way how to load item number and quantity for all 52 weeks with 0 in weeks for which there is no record in the qvd file and enter actual quantity if record exists? We have tried to modify SQL select for qvd file, but it is not an option. I'm running out of ideas how to archive this functionality. I tried also do while below, but without success.

SET vWEEKS=1;

DO while $(vWEEKS)<52

LINES_CONS:

LOAD ITNO, IF(WEEK=$(vWEEKS),WEEK,$(vWEEKS)) as WEEK, IF(QTY<>),QTY,0) as QTY

FROM

\\DATA\file.qvd

(qvd);

Do you have any advice for me how to proceed with this load? Many thanks in advance.

QVD Data                                 What I need.

Week	Item No.	Quantity
1	A	5
1	B	3
1	C	5
2	B	7
2	C	12
3	A	4
3	C	56
5	C	34

Week	Item No.	Quantity
1	A	5
1	B	3
1	C	5
2	A	0
2	B	7
2	C	12
3	A	4
3	B	0
3	C	56
4	A	0
4	B	0
4	C	0
5	A	0
5	B	0
5	C	34

[maxgro]

RESULT

SCRIPT

// load source data

Source:

LOAD Week,

     [Item No.],

     Quantity

FROM

[https://community.qlik.com/thread/189927]

(html, codepage is 1252, embedded labels, table is @2);

// make all weeks x all item (full outer join), no quantity field

Tmp:

load min(Week) as MinWeek, max(Week) as MaxWeek

Resident Source;

LET vMinWeek=Peek('MinWeek');

LET vMaxWeek=Peek('MaxWeek');

DROP Table Tmp;

WeeksXProduct:

load $(vMinWeek) + RowNo() -1 as Week

AutoGenerate ($(vMaxWeek)-$(vMinWeek)+1);

join (WeeksXProduct)

load Distinct [Item No.]

Resident Source;

// add quantity field from source

left join (WeeksXProduct)

load Week, [Item No.], Quantity

Resident Source;

// replace null (no quantity in source data) with 0

Final:

NoConcatenate load Week, [Item No.], alt(Quantity,0) as Quantity

Resident WeeksXProduct;

DROP Table WeeksXProduct;

[MarcoWedel · Accepted solution]

Hi,

one solution could be also:

tabQVDData:
LOAD * FROM [https://community.qlik.com/thread/189927] (html, codepage is 1252, embedded labels, table is @2);

Left Join (tabQVDData)
LOAD Distinct Week, [Item No.], AutoNumberHash128(Week, [Item No.]) as TempID Resident tabQVDData;

tabTemp:
LOAD RecNo() as Week AutoGenerate 52;

Left Join (tabTemp)
LOAD Distinct [Item No.] Resident tabQVDData;

Concatenate (tabQVDData)
LOAD Week,
    [Item No.],
    0 as Quantity
Resident tabTemp
Where not Exists(TempID, AutoNumberHash128(Week, [Item No.]));

DROP Field TempID;
DROP Table tabTemp;

hope this helps

regards

Marco

[petter]

Yet another solution could be - without the use of joins but using ApplyMap() and a Mapping Table:

[jonathandienst]

Also read this article:

How to populate a sparsely populated field

[#6]

Thanks a lot for advices. I will look at it later today and I will let you know. I really appreciate such a nice reaction from the comunity.

[#7]

Hi,

Thanks. I used this one as a solution, because it was easier for me to understand the logic behind, but all solutions here were helpfull, because it shows me different ways how to resolve the issue. Also the article provided below was usefull for me. Thanks to all who replied me.