Using IF / OR statements in the script

[#1]

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

[MayilVahanan]

HI

try like this

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

Hope it helps

[vivek_niti · Accepted solution]

Hi,

Try using..

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

Regards,

[nilesh_gangurde]

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

[#5]

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 ,

as ,

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

as ,

as

FROM

(ooxml, embedded labels, table is REPORT_2770)

So thanks for the reply but I am still stuck.

Steve

[#6]

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

Steve

[#7]

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

[vivek_niti]

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

[#9]

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

[nilesh_gangurde]

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

-Nilesh