topic Re: Using IF / OR statements in the script in QlikView

Using IF / OR statements in the script

Fri, 26 Apr 2013 16:01:56 GMT

I need ro filter out some information in the load script in QV.

Using the IF statement has worked well for me when thee is just one value that I want to load; for example:

if([Has Active CDs]='Y',[Has Active CDs]) as [Has Active CDs-2770],

Loads just the records where that column has a value of "Y"

But how do I do it if I want to load three different values for a given column? for example:

if([Bank Status]='Active',[Bank Status]) as [Bank Status-2770],

works fine to load the value "Active", but how would I load three values, Active, Failed and Merged?

On a related note, I have another column where I want to load the column but I do not want to load any data at all. How would I do that?

Thanks in advance,

Steve

Re: Using IF / OR statements in the script

MayilVahanan — Fri, 26 Apr 2013 16:07:30 GMT

try like this

if(Match([Bank Status],'Active','Failed','Merged'),[Bank Status]) as [Bank Status-2770],

Hope it helps

Re: Using IF / OR statements in the script

vivek_niti — Fri, 26 Apr 2013 16:09:08 GMT

Hi,

Try using..

if(WildMatch([Bank Status],'Active','Failed','Merged'),[Bank Status]) AS [Bank Status-2770]

Regards,

Re: Using IF / OR statements in the script

nilesh_gangurde — Fri, 26 Apr 2013 16:17:40 GMT

there are two ways to do this:

first,

if(wildmatch([Bank Status],'Active','Failed','Merged'),[Bank Status]) as [Bank Status -2770]

you can use Match() function but the match function is Case Sensitive. and Wildmatch() will not case sensitive.

second,

if([Bank Status] = 'Active' or [Bank Status] ='Failed' or [Bank Status] = 'Merged', [Bank Status]) as [Bank Status -2770]

-Nilesh

Re: Using IF / OR statements in the script

Fri, 26 Apr 2013 16:49:07 GMT

Thanks for the reply,

Your first option did not load any results at all for me. Please note I did make one change: if(wildmatch(='Active','Failed','Merged'),[Bank Status]) as , I added the “=” sign. I got an error without it.

The second option generates the following error when I try to reload the script:

Error in expression:

')' expected

LOAD ,

as ,

if(='Active'or[Bank Status]='Failed'or[Bank Status]='Merged',[Bank Status]) as ,

as ,

Territory as ,

State as ,

as ,

IF(='Y',[Has Active CDs]) as ,

as ,

FROM

(ooxml, embedded labels, table is REPORT_2770)

So thanks for the reply but I am still stuck.

Steve

Re: Using IF / OR statements in the script

Fri, 26 Apr 2013 16:50:19 GMT

As mentioned in an earlier email, this generates zero results. None of the desired values are loaded.

Steve

Re: Using IF / OR statements in the script

Fri, 26 Apr 2013 16:51:31 GMT

This appears to need an “=” sign before it will be accepted and when reloaded, this approach does not load any of the desired values.

Thanks,

Steve

Re: Using IF / OR statements in the script

vivek_niti — Fri, 26 Apr 2013 17:04:21 GMT

Hi,

The code works fine...

See the below example..

tab1:

LOAD * INLINE [

BankStatus

Active

Failed

Merged

Deactive

Demerged

];

tab2:

LOAD *,

If(WildMatch(BankStatus,'Active','Failed','Merged'),BankStatus) AS [New Status]

RESIDENT tab1;

DROP TABLE tab1;

Vivek

Re: Using IF / OR statements in the script

Fri, 26 Apr 2013 18:13:08 GMT

Boy do I feel Stupid…I found my error.

Let me reiterate…MY error. You all were correct. I just can’t type it seems.

So than you all for you help and your perseverance in dealing with an idiot newbie.

Best regards to all,

Steve

Re: Using IF / OR statements in the script

nilesh_gangurde — Fri, 26 Apr 2013 18:29:37 GMT

Request you to please mark the post as helpful ans or correct ans.

-Nilesh