topic Trouble with OR in QlikView

Trouble with OR

Wed, 10 Feb 2010 09:32:39 GMT

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

Trouble with OR

lironbaram — Wed, 10 Feb 2010 14:13:07 GMT

you need to write it like that

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

itws not nice but its works

Trouble with OR

disqr_rm — Wed, 10 Feb 2010 15:12:05 GMT

You have to mention field name again. Try following:

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

Trouble with OR

Wed, 10 Feb 2010 15:14:26 GMT

Hey,

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

Cheers

Lee

Trouble with OR

johnw — Thu, 11 Feb 2010 00:32:41 GMT

Or...

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

Trouble with OR

Thu, 11 Feb 2010 09:00:26 GMT

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

Trouble with OR

Tue, 23 Feb 2010 15:49:51 GMT

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)))

Trouble with OR

johnw — Tue, 23 Feb 2010 21:53:07 GMT

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?

Trouble with OR

Wed, 24 Feb 2010 09:16:43 GMT

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.

Trouble with OR

johnw — Sat, 27 Feb 2010 00:53:11 GMT

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.

Trouble with OR

Thu, 15 Apr 2010 10:41:19 GMT

Hi John,

Going back a little now but the below is still not returning correctly. I have a record that has a PA_Status of 'I' and a Claim_Status of 'U', it is being included in the calculation but I believe that according to the below it shouldn't be.

Do you have any suggestions what I have done wrong here ... it must be something simple that I am overlooking.

Sum(

IF(

match(PA_Status, 'C', 'I', 'O') AND

match(Claim_Status, 'E', 'H', 'O','R', 'B'),

Approved_LC))

Cheers

Lee

Sorry, just to add to that ... if I type it out the ugly way as below ... it returns the record as well ... I have done a view in the database and have pasted the results in below to prove that I am not mad ...

=IF((PA_Status = 'C' OR PA_Status = 'I' OR PA_Status = 'O') AND (Claim_Status = 'E' OR Claim_Status = 'H' OR Claim_Status = 'O' OR Claim_Status = 'R' OR Claim_Status = 'B'), Activity_ID)

ID Claim_Number PA_Status Claim_Status

1580 1217 I U

1580 1305 I U

Sorry ... solved it ... is was something stupid after all. Reload had not worked properly so the status was correct in SQL but incorrect in QlikView.