"Not Like" Equivalent or Workaround Within Set Analysis

[#1]

Hello Qlikviewers

I have a report at the moment that I need to exclude values that have 'Serv', 'Service', 'Front' and 'Fronting' within the name from Set Analysis.

At the moment the expression I'm using is very basic

Sum(Value)

So I'm just wondering if there is a SQL NOT LIKE equivalent within set analysis that I can use to remove values that have the above within the name?

[swuehl · Accepted solution]

You can look at the minus operator - in combination with search expressions for your field values:

Sum({<NameField -= {"*Serv*","*Front*"}>} Value)

[p_verkooijen]

Or you can create flagfields in the script, this way you can select these values using SUM{<_ValidFlag={1}>}Value)

Also check out Set Analysis Wizard for QlikView | qlikblog.at

[Anonymous]

Been using the Set analysis minus operator earlier today and it's saved my bacon.

[p_verkooijen]

Also check Set Analysis: syntaxes, examples‌

Qlik Community Tip: Marking Replies as Correct or Helpful

[HirisH_V7]

Hi,

may be like this,

sum( {$<AreaField = E({1<NameField={Serv', 'Service', 'Front' , 'Fronting' }>})>} Value )

HTH,

Hirish

[#7]

Thank you my expression now looks like this

Sum({<NameField -= {'*Serv*','*Front*','*Service*','*Fronting*'}>} Value)

Hopefully this works as its supposed to

[swuehl]

"*Serv*" should also find the same values like "*Service*", right (and similar for Front/ Fronting)?

That's why I only used the shorter searches with wildcards.