topic Re: How to use Null() into a set analysis expression in QlikView

How to use Null() into a set analysis expression

Mon, 03 Nov 2014 10:27:41 GMT

Hi , I have this set analysis expression but the values in 'HOTEL_AREA' are Null() so the expression is not filtering as expected.

How could I write correctly the set analysis expression to consider empty or Null() values instead of {''}?

-SUM({<HOTEL_AREA={''},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

Thank you.

Re: How to use Null() into a set analysis expression

alexandros17 — Mon, 03 Nov 2014 10:31:33 GMT

You cannot manage directly null values in set analysis but you can use '*' to set all as you 've done, there is another option, if your HOTEL_AREA field is used as dimension you can exclude nul values by checking the box in the dimension page.

Let me know

Re: How to use Null() into a set analysis expression

Gysbert_Wassenaar — Mon, 03 Nov 2014 10:32:33 GMT

You can try using -={'*'}. A better solution is to replace the nulls in the script with real values.

Re: How to use Null() into a set analysis expression

anbu1984 — Mon, 03 Nov 2014 10:43:46 GMT

SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

Re: How to use Null() into a set analysis expression

MK_QSL — Mon, 03 Nov 2014 10:59:11 GMT

Create one more field in the script just below the HOTEL_AREA

IF(IsNull(HOTEL_AREA) or LEN(TRIM(HOTEL_AREA))=0, 1, 0) as HOTEL_AREA_NULL_FLAG

Now use as below

=SUM({<TIPO_EMPRESA = {'HOTEL'}, HOTEL_AREA_NULL_FLAG = {'1'}>}REAL_PERIODO_USD)

Re: How to use Null() into a set analysis expression

Mon, 03 Nov 2014 11:16:36 GMT

I wish the easiest solution (HOTEL_AREA-={'*'}) had worked but it doesn't , I do not know why.

The solution "SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'} " works ok, but I don't understand why, I do not understand its logic but is usefull in this case.

because I have other fields with null vaues, I wonder if there is a syntanxis in script to replace all the "len(trim('any_field))=0" by ''.

Something like:

Table:

load

if(len(trim(*))=0 , '' , * ) as *

from ...

Re: How to use Null() into a set analysis expression

Gysbert_Wassenaar — Mon, 03 Nov 2014 11:21:52 GMT

You replace nulls in fields in the script by adding these two lines at the start of the script:

NullAsValue *;

Set NullValue = 'NULL' ;

Re: How to use Null() into a set analysis expression

Mon, 03 Nov 2014 11:58:33 GMT

if u are using "HOTEL_AREA" as dimension ...

just use "supress null values"

Re: Re: How to use Null() into a set analysis expression

anbu1984 — Mon, 03 Nov 2014 13:58:25 GMT

SUM({<REAL_PERIODO_USD={'=Len(Trim(HOTEL_AREA))=0'} --You will get correct result only when REAL_PERIODO_USD is unique across the rows.

Create ID using RowNo() in script and try this

=SUM({<ID={'=Len(Trim(HOTEL_AREA))=0'},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)

Create flag in script as below and use that in Set Analysis

Len(Trim(HOTEL_AREA)) As HOTEL_AREA_NULL_FLAG

=SUM({<HOTEL_AREA_NULL_FLAG={0},TIPO_EMPRESA={'HOTEL'}>}REAL_PERIODO_USD)