Select NULL value in pivot table

[pdumas]

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

[#2]

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:

[pover]

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

NullAsValue Code;

or

NullAsValue *;

Regards.

[pdumas]

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.

[#5]

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.

[pover]

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.