topic Re: cross table problem in QlikView

cross table problem

woshua5550 — Tue, 01 Aug 2017 08:31:46 GMT

Hello everyone

plz look at my source data first

I would like to load it by cross table function , here is my script

then I got the result as below

Model "C" and "D" just disappeared because they contains no value , but actually I need to load them into my qvw file and just leave the amount as null

how should I change my script ? plz help . thx ~

Re: cross table problem

tomasz_tru — Tue, 01 Aug 2017 08:54:35 GMT

Try this:

SET NullValue = 0;
NullAsValue *;

tmp:
LOAD
    *
FROM [lib://lib/1.xlsx]
(ooxml, embedded labels, table is 工作表1);

crosstable (Model, Amount, 1)
LOAD * RESIDENT tmp;
DROP TABLE tmp;

Re: cross table problem

tomasz_tru — Tue, 01 Aug 2017 08:55:39 GMT

of course you can set NULL to any other value - even empty string: SET NullValue = '';

Re: cross table problem

woshua5550 — Tue, 01 Aug 2017 09:25:03 GMT

Hi ! thank you for your reply

but it seems that empty string not equals to NULL value , I notice the information density of "Amount" is 100% which means field "Amount" contains no null value

anyway to make it as NULL ? I tried "SET NullValue = Null()" and "LET NullValue = Null()" but both don't work

Re: cross table problem

woshua5550 — Tue, 01 Aug 2017 09:37:30 GMT

Actually your answer is quite close to perfect ,it works too, just some confusions

1. what is the different between SET and LET

2. why you need a "tmp" table rather then load from Excel directly

Re: cross table problem

tomasz_tru — Tue, 01 Aug 2017 09:50:50 GMT

1. QlikView Addict: SET vs. LET

2. It looks like Crosstab is taking a shotcut, and omits Nulls before NullAsValue is applied.