cross table problem

[woshua5550]

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 ~

[tomasz_tru · Accepted solution]

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;

[tomasz_tru]

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

[woshua5550]

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

[woshua5550]

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

[tomasz_tru]

1. QlikView Addict: SET vs. LET

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