topic Select NULL value in pivot table in QlikView

Select NULL value in pivot table

pdumas — Fri, 03 Dec 2010 22:43:28 GMT

Hi,

I would like to be able to select a line in a pivot table that contains "null" values in the first dimention :

Suppose we have 3 fields from 2 tables :

Table A : Fields "Code" and "Join"

Table B : Fields "Join" and "Value"

The Pivot table has 2 dimensions : "Code" and "Join", One expression : sum(Value)

If a "join" in table B has no corresponding "Code" in table A, a "null" line is present in the pivot table first column.

What should be done so this "null" line becomes selectable?

Thanks

Pierre

Select NULL value in pivot table

Sat, 04 Dec 2010 00:46:18 GMT

Hi Dumas,

QV feature is that null values are not selectable at all,

anyway you can make some workaround:

TableTemp:
//TableA:
LOAD * INLINE [
Join, Code
a, 1
b, 2
b, 3
c, 4
d, 5
];
join
//TableB:
LOAD * INLINE [
Join, Value
a, 100
b, 200
b, 500
e, 500
f, 700
];

Table:
Load
Join,
Value,
if(isnull(Code),'N/A',Code) as Code,
1 as temp
resident TableTemp;

Drop field temp; // Drop dummy temp field
Drop table TableTemp;

Result is:

Select NULL value in pivot table

pover — Sat, 04 Dec 2010 00:57:58 GMT

You could also add the following line at the start of your code:

NullAsValue Code;

NullAsValue *;

Regards.

Select NULL value in pivot table

pdumas — Sat, 04 Dec 2010 12:37:25 GMT

Thanks for your help

The script resolution adds a to much code and complexity. I would like to avoid it.

I have added a calculated field that allows "null" select, however, unselect does not work fine.

Any suggestion?

Thanks

Pierre.

Select NULL value in pivot table

Mon, 06 Dec 2010 15:14:21 GMT


Karl Pover wrote:
You could also add the following line at the start of your code:
NullAsValue Code;
or
NullAsValue *;
Regards. <div></div>

In my experience, this will only work on tables separately. It will not create null values for joined tables/fields (where there would be missing connections). If you only have one table, it would work.

Select NULL value in pivot table

pover — Mon, 06 Dec 2010 18:23:18 GMT

You observation is right Carlgade. NullAsValue seem to only work for null values that originate at the datasource and not the null values generated in QlikView.

Here's an example of selecting null value with a bookmark instead of the calculated list box.

Regards.