topic Re: SQLs WHERE IN functionality but for an expression in QlikView

SQLs WHERE IN functionality but for an expression

Wed, 26 Mar 2014 11:53:14 GMT

In an expression I am carrying out something like the following:

IF(AGENT='James' OR AGENT='Adrian' OR AGENT='Neil', 1, 0)

Is there a way I can check AGENT against all of those values at once? I tried something like

IF(AGENT IN ('James','Adrian','Neil'), 1, 0)

But it didn't work. Ideally I'd like to store the array of names in a variable, so I could have something like

IF(AGENT = $vAgentsAllowed, 1, 0)

If $vAgentsAllowed was, say something like ('James','Adrian','Neil')

Can this be done? Thanks, George.

Re: SQLs WHERE IN functionality but for an expression

its_anandrjs — Wed, 26 Mar 2014 12:00:29 GMT

Use like IF(Match(AGENT,'James','Adrian','Neil') 1, 0)

Hope this helps

Thanks & Regards

Re: SQLs WHERE IN functionality but for an expression

PrashantSangle — Wed, 26 Mar 2014 12:01:01 GMT

Hi,

You can use Wildmatch()

Try This,

If(Wildmatch(AGENT,'James','Adrian','Neil'),1,0)

Regards,

Re: SQLs WHERE IN functionality but for an expression

Wed, 26 Mar 2014 12:06:08 GMT

Thanks, that works perfectly. But how would I put the array of names into a variable and use that variable in the Match() function? Also, How does the Wildmatch() function differ from the Match() function?

Re: SQLs WHERE IN functionality but for an expression

PrashantSangle — Wed, 26 Mar 2014 12:10:36 GMT

Hi,

There is only one difference between Match() and Wildmatch()

Wildmatch():

You can Use Special Character like * and ?

as well Wildmatch () contain case Insensative data

You can not do same with match()

Regards,

Re: SQLs WHERE IN functionality but for an expression

Wed, 26 Mar 2014 12:13:42 GMT

That makes sense, so could you let me know how to use a variable with Wildmatch()?