If and or statement

[sakshikaul]

Hi,

Below statement is giving an error

IF([Cost  excl. Tax]<=0,'Excluded','Check'='Excluded' or      if([Damage Tag]<>1 and ([Cost  excl. Tax]/Quantity<=1000),'Excluded','check')='Excluded' or      if( ApplyMap('Claim_Type',"Claim type [PAR]",'check')='EXCLUDED' or      if([Damage Tag]<>1,ApplyMap('Part_Number',Material,'check'))='EXCLUDED'),'EXCLUDED PART','REQUIRED PART'  as  A,

[DavidM]

Your statement should be written like this, but I still don't think this will work:

IF([Cost excl. Tax]<=0,'Excluded','Check'='Excluded'
or if([Damage Tag]<>1 and ([Cost excl. Tax]/Quantity<=1000),'Excluded','check')='Excluded' or
if( ApplyMap('Claim_Type',"Claim type [PAR]",'check')='EXCLUDED' or
if([Damage Tag]<>1,ApplyMap('Part_Number',Material,'check'))='EXCLUDED','EXCLUDED PART','REQUIRED PART')) as A

I suppose you are using this with load, so this is how you do it:

IF([Cost excl. Tax]<=0,'Excluded', //checks the first condition, if true then Excluded else
if([Damage Tag]<>1 and ([Cost excl. Tax]/Quantity<=1000),'Excluded',  //checks both conditions, if true Excluded, else

if( ApplyMap('Claim_Type',"Claim type [PAR]")='EXCLUDED','Excluded',  //this is where I have trouble finding out what you want to do. however if the map give value EXCLUDED it changes to Excluded, else

if([Damage Tag]<>1,ApplyMap('Part_Number',Material),'EXCLUDED PART')))) as A //and the last condition is strangest. Now if the condition is true, it uses ApplyMap, if not value is Excluded part

Hope this helps

[sakshikaul]

Hi,

Thanks for the reply.

Basically I have excel formulas for each condition which i have converted to qlikview script like 

condition 1-=excel formula ---> =IF(AF22<=0,"EXCLUSIVE","CHECK")

converted to qlikview  script--->if([Cost excl. Tax]<=0,'Excluded','check' ) as Debit note/cancelled claim ( - cost values),

condition 2= excel formula---> =AJ22IF(AF22<=0,"EXCLUSIVE","CHECK")

converted to qlikview script----> if([Damage Tag]<>1 and ([Cost excl. Tax]/Quantity<=1000),'Excluded','check' as Consequential parts:Per part cost<=1000 status,

condition 3 = excel formula--->=IFERROR(VLOOKUP(V22,MASTER!A:C,3,0),"CHECK")

converted to qlikview script--->  ApplyMap('Claim_Type',"Claim type [PAR]",'check') as excluded claim type,

condition 4 =excel formula ---> =IFERROR(IF(E22<>"1",(VLOOKUP(Z22,MASTER!$G:$I,3,0)),"CHECK"),"CHECK")

converted to qlikview---> if([Damage Tag]<>1,ApplyMap('Part_Number',Material,'check'),'check)' as excluded parts 

finally, now I want to combine all the above conditions into one line and create a new field in qlikview so for that given excel formula is

condition 5

=IFERROR(IF(OR(AH22="EXCLUSIVE",AJ22="EXCLUSIVE",AK22="EXCLUSIVE",AL22="EXCLUSIVE"),"EXCLUSIVE PART","REQUIRED PART"),"EXCLUSIVE PART")

qlikview script----> as suggested by you is giving wrong output

F([Cost excl. Tax]<=0,'Excluded', //checks the first condition, if true then Excluded else
if([Damage Tag]<>1 and ([Cost excl. Tax]/Quantity<=1000),'Excluded',  //checks both conditions, if true Excluded, else

if( ApplyMap('Claim_Type',"Claim type [PAR]")='EXCLUDED','Excluded',  //this is where I have trouble finding out what you want to do. however if the map give value EXCLUDED it changes to Excluded, else

if([Damage Tag]<>1,ApplyMap('Part_Number',Material),'EXCLUDED PART')))) as A //and the last condition is strangest. Now if the condition is true, it uses ApplyMap, if not value is Excluded part

Please check all the above given conditions and help me in combing all the conditions(creating condition 5, as stated above) in one line in qlikview script.

I dont know where I am going wrong.

[DavidM]

You cannot combine those statements with OR, it makes no sense. Condition 3 is no condition, just mapping with 'check' for values which are not found in map. Maybe this will work:

iF([Cost excl. Tax]<=0,'Excluded',
if([Damage Tag]<>1 and ([Cost excl. Tax]/Quantity<=1000),'Excluded',

if([Damage Tag]<>1,ApplyMap('Part_Number',Material,'check'),

ApplyMap('Claim_Type',"Claim type [PAR]",'check')))) as A

This way you will get values Excluded and Check but there is no other value (except those from Applymap). Basically whatever doesn't fit the first two conditions will go the last

[sakshikaul]

Hi,

After I applied above logic I am getting output as follows(as given in below image) 

In above image Why i am getting three values ?

I should get only two values as output ie excluded and required part (as stated in below image)

Plesae help

[sakshikaul]

@sunny_talwar  Please help

[NitinK7]

Hi 

instead of below condition 

Use 

If( Match( ApplyMap( 'Claim_Type' , Claim type [PAR]) ,'EXCLUDED' ), 'Excluded' ) 

[sunny_talwar]

Would you be able to provide the Excel behind this and provide the output that you expect to see from the sample provided?

[sakshikaul · Accepted solution]

Dear Sunny

I was able to handle all the above conditions in Qlikview 

Thanks 

Sakshi Kaul