topic Re: Handling Null values in QlikView

Handling Null values

polisetti — Mon, 17 Feb 2014 21:41:58 GMT

Hi ,

In the data base for customers i have target flag as 1 and 0 and for some customers the target flag is not populated .In my report i have a straight table in which i need to show all customers and targets flag and sales .I have list box selection for target flag ,in which i need to show 1 as 'Target ' & 0 as 'Non-Target ' and Null customers as also 'Non-target'.Same should be shown in straight table also .

So in calculated dimension i wrote as 'if(Not Isnull([Target flag]) and [Target Flag]=1,'Target','Non-target'))'.

Same i kept it in List box also .

But the issue is when i select on 'null ' customer it should link to 0 value of target flag but list box is griding out ,i want to link that null customers as 0 or non-target .So should i write any expression in Edit script .Please give any suggestions.

* And in desktop version and web view the null is showing as Non-target ,but when i check at development ,there the null customers are showing as null.

Please check the screen shots one is at deskop application and one is at development server .

Re: Handling Null values

santharubban — Mon, 17 Feb 2014 21:55:40 GMT

Try this,

if([Target Flag]>0,,'Target','Non-target')

if this doesn't help u, upload sample appilication.

Re: Handling Null values

stevedark — Mon, 17 Feb 2014 22:26:02 GMT

Hi there,

Sorting things in the load script is almost always better than doing it in a calculated dimension. You physically can not select a Null as it does not exist. Even if you display it as something else (using a calculated dimension) it is still null behind the scenes.

In your load script have the following lines:

if(isnull([Target flag]), 0, [Target flag]) as [Target flag],

if([Target flag] = 1, 'Target', 'Non-Target') as [Is Target],

You can then use the Target flag in expressions and Is Target as a dimension and select on fields as you would want.

If you have blanks rather than nulls in the field (it sounds like they are not) you will need to check for null and blank separately in the load script.

Hope that helps.

Steve

Re: Handling Null values

maxgro — Mon, 17 Feb 2014 23:03:12 GMT

in load script

load

....

if(len(trim([Target flag]))=0 or [Target flag]=0, dual('Non target', 0), dual('Target', -1)) as [Target flag],

...