Trouble with OR

[#1]

Hi,

I am trying to put together a basic table that gives me the sum of a field where the status meets certain criteria - see below

sum(if(PA_Status = 'O',Approved_LC)) ... this works !

sum(if(PA_Status = 'O' or 'C', Approved_LC)) ... this does not include the rows where the PA_Status is C ...

What is QlikView looking for here in place of OR. Could I use IN('C','O')?

Thanks

Lee

[lironbaram · Accepted solution]

hi

you need to write it like that

sum(if(PA_Status = 'O' or 'PA_Status = 'C', Approved_LC))

itws not nice but its works

[disqr_rm]

You have to mention field name again. Try following:

sum(if(PA_Status = 'O' or PA_Status = 'C', Approved_LC)) ...

[#4]

Hey,

Thanks for the posts, that works a treat ...

Cheers

Lee

[johnw]

Or...

sum(if(match(PA_Status,'O','C'),Approved_LC))

[#6]

Ooh ... I was so close ... I tried match() having done some research and spotted Rob's blog on it but I had written sum(match()) and missed the if out ...

[#7]

So following on from the use of match ... I am trying to combine two match statement within an IF statement to return a specific value against multiple records ... it is not quite working. Can anyone help with a quick pointer

=if(CurrencySelection = 'Local', sum(IF(match(PA_Status,'O','C','I') AND match(Claim_Status,'E' , 'H' , 'O' , 'R' , 'U' , 'B'),Approved_LC)), sum(IF(match(PA_Status,'O','C','I') AND match(Claim_Status,'E' , 'H' , 'O' , 'R' , 'U' , 'B'),Approved_PC)))

[johnw]

Well, my computer ate my reply, but basically, I don't see anything wrong. What are you seeing, and what did you want to see instead?

[#9]

Hi John,

The second match statement against Claim_Status is not being taken into account, it's like the AND function is not working ... basically, it is not adding up to what I know it should.

I have also tried match(PA_Status .... AND Claim_Status ...), so incorporating the AND into the the single match() function with the same result.

[johnw]

They should be separate match() functions. So match(field1,value1,...) and match(field2,value2,...), which is what you're doing.

Maybe you have a problem with Claim_Status. For instance, does it actually have a value of 'E ' instead of 'E'? Maybe those wouldn't match. You could do a trim(Claim_Status) in that case. Caps vs. lower case? What happens if you write it out the long, ugly way?

...sum(if(match(...) and (Claim_Status='E' or Claim_Status='H'...),Approved_LC))...

If THAT works, then I'm really at a loss, since as best I can tell, that's exactly what your match() function should be doing. If it doesn't work, then it tells you the problem isn't with the match() at all, but rather with the values you're comparing.