Tuesday, November 4, 2014

QV20 Filter the null values of column in set analysis

I have SCOTT.EMP table with us, and want to filter out null values .
the basic requirement is to filter the data using set analysis whose MGR is null.

Dimension - MGR
expression - SUM (SAL)

Now i want to remove null's from dimension of chart (i.e. sum= 5000 where MGR is null),
i can do in various ways but i want to use using "set analysis"

expression - sum({<MGR-={'=Len(Trim(MGR))=0'} >} [SAL])

QV19 Replace all null values

Null values in database can lead a big problem while calculating the dimensions.
In other word we can say that it is not good if database having null values in it.

It is good if we remove all null values. in Qlikview You can set a variable which will replace all null value with some string.

In very start of script/ Main tab of your document you need to add

NullAsValue *;
Set NullValue = 'NULL' ;

and then reload the document.

Below is the image will show data behaviour "COMM" column before and after adding variable.

web stats