topic Re: What is the error in this expression? in QlikView

What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 21:19:17 GMT

HI All,

I am not able to figure out the issue with this expression:

=Count({<upper([Curr Status])={'MET'}>}Key)

Please help.

Thanks

Sam

Re: What is the error in this expression?

sunny_talwar — Fri, 13 Feb 2015 21:21:00 GMT

You cannot use a function (Upper) before = in your set modifier.

You might want to do it in the script itself or use MET as it may be spelled in your database.

HTH

Best,

Re: What is the error in this expression?

maxgro — Fri, 13 Feb 2015 21:38:46 GMT

maybe without set analysis

count(if(upper([Curr Status])='MET', Key))

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 21:58:33 GMT

I think that it is not case sensitive at all, that is there is no difference between

=Count({<[Curr Status]={'MET'}>}Key)

and

=Count({<[Curr Status]={'met'}>}Key)

Re: What is the error in this expression?

Oleg_Troyansky — Fri, 13 Feb 2015 22:34:33 GMT

And if you really want to insist on UPPER, you can do it like this:

=Count({<[Curr Status]={"=UPPER([Curr Status])='MET' "}>} Key)

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 22:42:35 GMT

Thanks all for your response!

Re: What is the error in this expression?

maxgro — Fri, 13 Feb 2015 22:48:18 GMT

Re: What is the error in this expression?

Oleg_Troyansky — Fri, 13 Feb 2015 23:01:21 GMT

You see Michael, it is case sensitive after all!

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 23:02:26 GMT

Thanks for testing!

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 23:11:34 GMT

I see, but it is very strange. I'm just re-testing this simple expression:

count ({<Field={"*abc*"}>} Field)

and

count ({<Field={"*ABC*"}>} Field)

They return the same exact result. The actual values in the Field are all capital.

Re: What is the error in this expression?

maxgro — Fri, 13 Feb 2015 23:21:40 GMT

added another one, *MET*

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 23:24:19 GMT

Now instead of enjoying Australian lamb and Californian wine, I have to test myself

OK, I tried with and without wildcard, with single and with double quotes and without any, and result is the same - not case sensitive.

So the difference that I use it in a text box and Massimo's are in a table. Would it matter?

Another assumption - QV version. I'm using 11.20 SR10.

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 23:33:03 GMT

Nice!

Adding one more, without wildcards, returns all three, to make it more confusing.

=count({$ <CurrStatus={'Met'}>} Key)

Re: What is the error in this expression?

Anonymous — Fri, 13 Feb 2015 23:43:53 GMT

Here is what's going on, at least what I can see now:

It is not case sensitive if use wildcards, or if the string without wildcards does not match exactly, case included, any of the existing values. If there are no wildcards and the string matches exactly one of the values, it is case sensitive.

I don't like the result. Gone drinkin'

Re: What is the error in this expression?

maxgro — Sat, 14 Feb 2015 00:11:02 GMT

11.20 SR10 64bit

I fully agree with "I don't like the result"

Re: What is the error in this expression?

Oleg_Troyansky — Sat, 14 Feb 2015 18:05:28 GMT

Hmmm, here are my results and some observations:

1. When the value is listed with no quotes, it looks like the search is case-sensitive (column 2 returns 1 value)

2. But, if the search value doesn't exist (such as mET), then the search reverts to not case-sensitive (column 4 returns 4 values)

3. The same happens when either single quotes or double quotes are used.

4. However, if a wildcard like "MET*" is used as a search (see the last column), then the search is not case-sensitive either way.

It smells like a bug. My conclusion - always keep in mind that the search may or may not be case sensitive and ensure proper logic using UPPER, LOWER, or Capitalize.

Re: What is the error in this expression?

hic — Tue, 03 Mar 2015 15:39:35 GMT

The logic is:

IF there is a field value that is an exact case-sensitive match THEN

RETURN this single field value

ELSE

RETURN all matches you get with a case-insensitive wildcard search

I consider this behaviour to be incorrect, i.e. a bug.

HIC

Re: What is the error in this expression?

Oleg_Troyansky — Tue, 03 Mar 2015 16:15:58 GMT

This behavior is, indeed, very annoying. You can get different results almost randomly. If your search string happens to exist in the data, you will get 1 value back. If it doesn't exist, you may get many more other values that match the case-insensitive search.

Don't you think we should make QlikView search more predictable???