Expression set analysis

[smilingjohn]

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 

[smith11484]

Set analysis predefines the SET OF DATA that our charts / tables use. So, using a Set Expression, we can tell our object (chart / table) to display values corresponding to various sets of data (e.g. a pre-defined time-period, geographic region, product lines etc.). 

Iraqi Dinar Recaps

[smilingjohn]

Hi Expertise any help on my post 

[Anonymous]

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

[smilingjohn]

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 

[Anonymous]

Perhaps this...

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

[smilingjohn]

Sorry but this is not working 

[MayilVahanan]

Hi

Can you provide the sample data and expected ouput

[edwin]

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

[edwin]

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

is 

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