topic Re: Load missing fields as 0 in QlikView

Load missing fields as 0

Mon, 02 Nov 2015 14:05:45 GMT

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

Re: Load missing fields as 0

maxgro — Mon, 02 Nov 2015 20:08:13 GMT

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;

Re: Load missing fields as 0

MarcoWedel — Mon, 02 Nov 2015 23:05:31 GMT

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

Re: Load missing fields as 0

petter — Tue, 03 Nov 2015 05:53:43 GMT

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

Re: Load missing fields as 0

jonathandienst — Tue, 03 Nov 2015 06:35:02 GMT

Re: Load missing fields as 0

Tue, 03 Nov 2015 07:03:50 GMT

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.

Re: Load missing fields as 0

Tue, 03 Nov 2015 09:42:54 GMT

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.