Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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
Highlighted
Specialist
Specialist

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
Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

Re: cross table problem

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

Highlighted
Creator III
Creator 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

Highlighted
Creator III
Creator 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

Highlighted
Specialist
Specialist

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.