topic how to use null in the set expression in QlikView

how to use null in the set expression

Mon, 21 Oct 2013 10:40:23 GMT

Hi Team,

As per the requirement, i have to use the null in the set expression. Can anyone please let me know how to use the NULL in the Set expression.

i have used the expression like,

Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN','NULL'}

Please let me know the correct usage of null in the set expression.

Thanks and Regards,

Smitha

Re: how to use null in the set expression

Mon, 21 Oct 2013 10:50:13 GMT

You should be able to catch null values just passing single quotes with no spaces as a Modifier value:

Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN',''}

This is in case null values have not been treated in Script layer in order to place a NULL text instead or something..

Re: how to use null in the set expression

tresB — Mon, 21 Oct 2013 10:50:45 GMT

Try like:

Asset={"=$(='Active Directory' or 'Iphone' or 'Microsoft Exchange' or 'VeriSign Certificate' or 'VPN' or IsNull(Asset))"}

Or,

<Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN'}> + <Asset={"=$(=IsNull(Asset))"}>

Hope somebody can suggest a simpler one.

Re: how to use null in the set expression

jagan — Mon, 21 Oct 2013 10:56:16 GMT

Hi Smitha,

In Script just add a flag for null like this

LOAD

Alt(Asset, 'N/A') AS TempAsset

FROM DataSource;

Now use this field in set analysis

=Count({<Asset=, TempAsset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN','NULL'}>} ID)

in set analysis

<Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN'}> + <Asset={"=$(=IsNull(Asset))"}>

Regards,

Jagan.

Re: how to use null in the set expression

Gysbert_Wassenaar — Mon, 21 Oct 2013 11:18:36 GMT

This should work:

<Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN'}> + <Asset-={'*'}>

But it's far easier to replace the nulls in the script:

Load

field1, field2, ...etc,

if(len(trim(Asset))=0,'n/a', Asset) as Asset

From ...mysource...;

Then use that n/a value in the set modifier:

<Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN','n/a'}>

Re: how to use null in the set expression

MayilVahanan — Mon, 21 Oct 2013 11:23:17 GMT

Try like this

<Asset={'Active Directory','Iphone','Microsoft Exchange','VeriSign Certificate','VPN'}> + <Asset-={'*'}>