Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

Expression set analysis

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 

Labels (1)
12 Replies
smith11484
Contributor II
Contributor II

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.). 
smilingjohn
Specialist
Specialist
Author

Hi Expertise any help on my post 

 

JohnInSD
Contributor III
Contributor III

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
Specialist
Specialist
Author

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 

JohnInSD
Contributor III
Contributor III

Perhaps this...

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

smilingjohn
Specialist
Specialist
Author

Sorry but this is not working 

MayilVahanan

Hi

Can you provide the sample data and expected ouput

Thanks & Regards,
Mayil Vahanan R
edwin
Master
Master

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

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