Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

woshua5550
Contributor III

cross table problem

Hello everyone

plz look at my source data first

微信截图_20170801162148.png

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

微信截图_20170801162333.png

then I got the result as below

微信截图_20170801162248.png

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 ~

Tags (1)
1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

Re: cross table problem

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;

View solution in original post

5 Replies
tomasz_tru
Valued Contributor

Re: cross table problem

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;

View solution in original post

tomasz_tru
Valued Contributor

Re: cross table problem

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

woshua5550
Contributor III

Re: cross table problem

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
Contributor III

Re: cross table problem

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
Valued Contributor

Re: cross table problem

1. QlikView Addict: SET vs. LET

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