topic Re: If and or statement in QlikView

If and or statement

sakshikaul — Wed, 12 Feb 2020 05:43:44 GMT

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,

Re: If and or statement

DavidM — Wed, 12 Feb 2020 07:35:16 GMT

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

Re: If and or statement

sakshikaul — Wed, 12 Feb 2020 09:14:38 GMT

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([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.

Re: If and or statement

DavidM — Wed, 12 Feb 2020 09:56:47 GMT

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

Re: If and or statement

sakshikaul — Thu, 13 Feb 2020 04:26:05 GMT

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

Re: If and or statement

sakshikaul — Sat, 29 Feb 2020 12:31:15 GMT

@sunny_talwar Please help

Re: If and or statement

NitinK7 — Sat, 29 Feb 2020 17:53:45 GMT

instead of below condition

Use

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

Re: If and or statement

sunny_talwar — Mon, 02 Mar 2020 13:26:23 GMT

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

Re: If and or statement

sakshikaul — Thu, 05 Mar 2020 09:38:34 GMT

Dear Sunny

I was able to handle all the above conditions in Qlikview

Thanks

Sakshi Kaul