Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
adamjewell
New Contributor

Exclude sum results in straight table when 3 conditions are all met together

Hi All,

I am new to Qlik and have hit a roadblock so please can someone help me.

I am trying to exclude the sum results in a straight table when 3 conditions are all met together, each of them from different dimensions. I only want to do the exclusions within this straight table as i need the full data for other tables

My 3 Dimensions are Side, BusinessArea and TerminalNo and i want to sum the number of transactions from a field called NoOfTransactions.

I have 30 Sites all with unique names, 10 BusinessAreas all with unique names and  100's of Terminal numbers but not with unique names e.g terminal 1 will exist at all sites.

So what i need to do is exclude the results for only:

terminal 2 at Site10 for BusinessArea5

terminal 9 at Site21 for BusinessArea5

....etc ( i have approx 10 combinations in total)

I tried some set analysis myself and could exclude terminal 2 but it excluded terminal 2 everywhere, likewise with the site it excluded the sites i listed but excluded all terminals at those site.

Please help, thanks.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Exclude sum results in straight table when 3 conditions are all met together

For example, if your two conditions were: terminal 2 at Site10 for BusinessArea5 and terminal 9 at Site21 for BusinessArea5


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, Site)


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, BusinessArea)


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, TerminalNo)


So if you wanted more conditions you'd add another "or ((TerminalNo = x) and (Site = y) and (BusinessArea = z))"

View solution in original post

6 Replies
Highlighted
Not applicable

Re: Exclude sum results in straight table when 3 conditions are all met together

You could create a flag in the script, so that if these three conditions are met, flag = 0, otherwise, flag = 1. Then in your expression use set analysis to exclude the fields with a 0 flag.

Highlighted
adamjewell
New Contributor

Re: Exclude sum results in straight table when 3 conditions are all met together

Hi Jacob,

Thanks for your response. I have lots of tables and already have lots of flags that apply to those so was hoping to avoid doing anything in the script just for this one table, as it's the only place it will ever apply.

I appreciate it may not be the best way but do you know if it can be done just within the table?

Highlighted
Not applicable

Re: Exclude sum results in straight table when 3 conditions are all met together

You could use an if statement in each dimension:

if((condition1) or (condition2) or... or(condition10), NULL, Site)

if((condition1) or (condition2) or... or(condition10), NULL, BusinessArea)

if((condition1) or (condition2) or... or(condition10), NULL, TerminalNo)

Highlighted
adamjewell
New Contributor

Re: Exclude sum results in straight table when 3 conditions are all met together

Hi Jacob,

I see what you are saying but not sure how/what i enter for the conditions, can you really dumb it down for me e.g

if((Site=Site10) or (BusinessArea=BusinessArea5) or (Terminal=terminal 2), NULL, Site)

Thanks for your help on this.

Highlighted
Not applicable

Re: Exclude sum results in straight table when 3 conditions are all met together

For example, if your two conditions were: terminal 2 at Site10 for BusinessArea5 and terminal 9 at Site21 for BusinessArea5


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, Site)


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, BusinessArea)


if(((TerminalNo = 2) and (Site = 'Site10') and (BusinessArea = 'BusinessArea5')) or ((TerminalNo = 9) and (Site = 'Site21') and (BusinessArea = 'BusinessArea5')), Null, TerminalNo)


So if you wanted more conditions you'd add another "or ((TerminalNo = x) and (Site = y) and (BusinessArea = z))"

View solution in original post

Highlighted
adamjewell
New Contributor

Re: Exclude sum results in straight table when 3 conditions are all met together

Hi Jacob,

Thanks very much for explaining that and all your help, it worked perfectly.