topic Re: Expression set analysis in QlikView

Expression set analysis

smilingjohn — Thu, 05 May 2022 08:52:38 GMT

Hi All,

This is related to my previous post , I have got how to consider the null values . Now I am using a set analysis in which i am comparing row wise data (Category=(Sub_Cat)) and the below expression is working perfectly fine ,

Now here I also need to include the null values from Category as well , how do I do this in the expression can anyone please help me in achieving this

=count(Distinct {<Status-={'Delivered},
Severity={'1 - Critical', }>}
if(OrderDate<DelDate and Category=(Sub_Cat)),ID))

thanks in advance

Re: Expression set analysis

smilingjohn — Thu, 05 May 2022 13:30:01 GMT

Hi Expertise any help on my post

Re: Expression set analysis

Anonymous — Thu, 05 May 2022 15:19:14 GMT

You could make the null values equal to something in your script, then call out that value in the Set Analysis too.

NullAsValue Category;

Set NullValue = 'Null';

When the script runs it will make all null values for Category equal to 'Null', and your expression might be;

=count(Distinct {<Status-={'Delivered},Severity={'1 - Critical', }>}if(OrderDate<DelDate and (Category=(Sub_Cat) or Category=('Null'))),ID))

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/NullAsValue.htm

Re: Expression set analysis

smilingjohn — Thu, 05 May 2022 15:57:10 GMT

Thanks for the reply John

In my case case the null values are in text format , therefore I have renamed all of them to NULL

Hence applied your logic but this don't work ...

Is there any other way to work around ...

Because the data is going in row wise comparision

Thanks

Re: Expression set analysis

Anonymous — Thu, 05 May 2022 16:39:28 GMT

Perhaps this...

=count({<Status-={'Delivered},Severity={'1 - Critical'}, OrderDate={'<$(=DelDate)'},Category={'Sub_Cat','Null'}>}Distinct ID))

Re: Expression set analysis

smilingjohn — Fri, 06 May 2022 06:24:07 GMT

Sorry but this is not working

Re: Expression set analysis

MayilVahanan — Fri, 06 May 2022 07:06:51 GMT

Can you provide the sample data and expected ouput

Re: Expression set analysis

edwin — Mon, 09 May 2022 15:54:26 GMT

you will need to clearly identify all your conditions. normally what JohnInSD suggested should work. where he added Null as a condition for Category. but this is assuming all the other conditions work for the NULL record. so you need to identify all the other criteria for your NULL record. for example if all these conditions are irrelevant to your NULL record then maybe you need:

=count({<Status-={'Delivered},Severity={'1 - Critical'}, OrderDate={'<$(=DelDate)'},Category={'Sub_Cat','Null'}>+<Category={'Null'}>}Distinct ID))

which does the same thing as an OR. if you need more conditions to go with the NULL, just add it in.

if this still does not work you may have to add sample data so there is less guess work on anyone wanting to help

Re: Expression set analysis

edwin — Mon, 09 May 2022 16:09:23 GMT

to clarify, your original expression is

Status<>Delivered and Severity<>1 - Critical and OrderDate <DelDate and Category=Sub_Cat

adding Null to sub cat is like:

Status<>Delivered and Severity<>1 - Critical and OrderDate <DelDate and (Category=Sub_Cat or Category =Null)

assuming that you wanted the original conditions and just include Category Null, the following set analysis:

{<Status-={'Delivered},Severity={'1 - Critical'}, OrderDate={'<$(=DelDate)'},Category={'Sub_Cat','Null'}>+<Category={'Null'}>}

Status<>Delivered and Severity<>1 - Critical and OrderDate <DelDate and Category=Sub_Cat or Category =Null

Re: Expression set analysis

edwin — Mon, 09 May 2022 16:10:10 GMT

this is of course assuming you replaced all null values with the word Null as suggested in earlier comments

Re: Expression set analysis

smilingjohn — Tue, 10 May 2022 09:15:22 GMT

Hi Edvin

Thanks for the reply and wonderful explanation from you above concept I think the highlighted part seems to be mistaken because Sub_Cat is a field and I am not sure why did you put it into single quotes

Thanks

Re: Expression set analysis

edwin — Tue, 10 May 2022 13:35:35 GMT

hi, now looking at your expression it does make sense that you use an if statement for that condition. i was picking up from a reply from someone else. regardless, the principle is that same. the idea im getting across is around organizing your conditions. whether you use set analysis or if statements, the principle is the same. what i am proposing is a solution approach, you need to tweak it in the implementation part. as you dont have a data model nor example, everyone will just be guessing