topic Is there a known bug with using both text AND numbers in an OR statement? in QlikView

Is there a known bug with using both text AND numbers in an OR statement?

Mon, 14 Feb 2011 17:43:40 GMT

I've come across a weird scenario and I'm wondering if anyone else has seen this or has worked around it.

In a WHERE clause I can successfully select a Sales Office that is all text with:

AND [Sales Office]='CAN'

I can also successfully select multiple Sales Offices with an OR statement:

AND ( [Sales Office] = '010' OR [Sales Office] = '021' OR [Sales Office] = '041')

When I add the textual Sales Office to the numerical ones as part of my OR statement, I only get numerical results coming through, thus:

AND ( [Sales Office] = '010' OR [Sales Office] = '021' OR [Sales Office] = '041' OR [Sales Office]='CAN')

Only gives me results back that are for '010' '021' or '041' - I do not get any data out of the QVD that I'm reading that pertains to Sales Office 'CAN'

Does anyone know if this is a bug or see any glaring mistakes in my clause?

Is there a known bug with using both text AND numbers in an OR statement?

Miguel_Angel_Baeyens — Mon, 14 Feb 2011 17:55:36 GMT

Hello,

I'd use the Match() function instead, that will take the value in the field and return greter than zero if any of the followgin values match exactly (case sensitive)

WHERE MATCH([Sales Office], '010', '021', '041', 'CAN') > 0;

Hope that helps.

Is there a known bug with using both text AND numbers in an OR statement?

Mon, 14 Feb 2011 21:03:23 GMT

Unfortunately this did not perform any differently. I still only show the numbered sales offices in the result.

Any other ideas?

Is there a known bug with using both text AND numbers in an OR statement?

Mon, 14 Feb 2011 21:29:22 GMT

Your numbers ARE TEXT because they are quoted. I think you have another issue.

Is there a known bug with using both text AND numbers in an OR statement?

Anonymous — Mon, 14 Feb 2011 21:33:56 GMT

I don't see any mistakes, and expect this to work.
One note though - it is case-sensitive, so the 'CAN' text must be exactly all cap in oyur source data. Or, you can try
... OR upper([Sales Office])='CAN'