Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

how to use null in the set expression

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

5 Replies
Not applicable

Re: how to use null in the set expression

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..

MVP
MVP

Re: how to use null in the set expression

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.

MVP & Luminary
MVP & Luminary

Re: how to use null in the set expression


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)

or

in set analysis

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

Regards,

Jagan.

MVP & Luminary
MVP & Luminary

Re: how to use null in the set expression

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'}>


talk is cheap, supply exceeds demand

Re: how to use null in the set expression

Hi

Try like this

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