Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;

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;

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.

Community Browser