topic Re: null values in Set analysis in QlikView

null values in Set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have the following expression I am using in straight table.

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}>

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}>}

DISTINCT Asset_Id)

It basically has two conditions each. I want those units which dont have contract or which have expired contract. Hence I am using '+' sign.

Now I want to add another condition which looks at contract status and excludes 'Active' or 'Inactive'. So, I am making my expression like below:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, EntStatus = {'*'} - {'Active','Inactive'}>

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, EntStatus = {'*'} - {'Active','Inactive'}>}

DISTINCT Asset_Id)

But by doing so it also excludes null values. I want to include null values and exclude just 'Active' and 'Inactive'.

Please help me.

Thank you

Parth

Re: null values in Set analysis

sunny_talwar — Wed, 05 Oct 2016 19:03:08 GMT

How about giving this a show:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, Asset_Id = {"=If(Match(EntStatus, 'Active', 'Inactive'), 0, 1) = 1"}>

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, Asset_Id = {"=If(Match(EntStatus, 'Active', 'Inactive'), 0, 1) = 1"}>}

DISTINCT Asset_Id)

Re: null values in Set analysis

Wed, 05 Oct 2016 19:10:22 GMT

Hi Sunny,

Thanks for your response. Still no luck.

-Parth

Re: null values in Set analysis

sunny_talwar — Wed, 05 Oct 2016 19:13:52 GMT

How about this:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, Asset_Id = {"=NOT EntStatus LIKE 'Active' and NOT EntStatus LIKE 'Inactive' "}>

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, Asset_Id = {"=NOT EntStatus LIKE 'Active' and NOT EntStatus LIKE 'Inactive' "}>}

DISTINCT Asset_Id)

Re: null values in Set analysis

Wed, 05 Oct 2016 19:17:18 GMT

Still the same result. Appreciate your help.

-Parth

Re: null values in Set analysis

sunny_talwar — Wed, 05 Oct 2016 19:19:02 GMT

Would it be possible to share a sample?

Re: null values in Set analysis

Wed, 05 Oct 2016 20:23:31 GMT

Hi Sunny,

Apologies for not getting back. Actually, I was testing the result. Both of the above answers worked. I was measuring a wrong expression before.

Again apologies for not testing the result thoroughly.

Again tank you very much for your help.

Best

Parth