topic Re: Set Analysis - is it case insensitive ? in QlikView

Set Analysis - is it case insensitive ?

Digvijay_Singh — Sat, 03 Oct 2015 08:53:06 GMT

I think I am missing some basics here but I thought I should get opinion here. I was going through one of the post in community and was trying to create sample like below but came across something looks silly but I think I am missing something in understanding it properly -

See the details below.

When I used below script -

LOAD * INLINE [

Flag Field, Status, Field A, Field B, Product ID

2, ACTIVE, Red, Color, 1

2, ACTIVE, BLUE, Curve, 2

1, DEACTIVATED, RED, Curve, 3

2, ACTIVE, Blue, Curve, 4

];

The output is like below -

When I changed the case of Field A value -

LOAD * INLINE [

Flag Field, Status, Field A, Field B, Product ID

2, ACTIVE, Red, Color, 1

2, ACTIVE, BLUE, Curve, 2

1, DEACTIVATED, RED, Curve, 3

2, ACTIVE, BLUE, Curve, 4

];

The output is as below -

When both values made to lowercase -

LOAD * INLINE [

Flag Field, Status, Field A, Field B, Product ID

2, ACTIVE, Red, Color, 1

2, ACTIVE, Blue, Curve, 2

1, DEACTIVATED, RED, Curve, 3

2, ACTIVE, Blue, Curve, 4

];

Output is same -

Re: Set Analysis - is it case insensitive ?

jagan — Sat, 03 Oct 2015 09:06:45 GMT

Hi,

Qlikview is case sensitive so BLUE is not equal to Blue, so which loading you can format the values by using either Upper(), Lower() or Capitalize().

Even in set analysis also it is case sensitive.

Regards,

Jagan.

Re: Set Analysis - is it case insensitive ?

Anonymous — Sat, 03 Oct 2015 09:19:12 GMT

Hi,

yes,Qlikview is case sensitive so BLUE and Blue values are not same,once inline tables are loaded,

Check FieldA filter in UI and you see two different values(BLUE and Blue),while loading you format the

values by using either Uppercase(), Lowercase() or Capitalize() and not with mixed cases else you get duplicate values.

so its same in set analysis also it is case sensitive.

if you have duplicate values same like these scenario,you need to do data cleanse in the script.

Same goes with data from different data sources.request your DB team to do data cleansing or else

you can do your self with if statement or Applymap functionality

Regards

Neetha

Re: Set Analysis - is it case insensitive ?

Digvijay_Singh — Sat, 03 Oct 2015 09:27:41 GMT

Thanks Jagan for the response. I was wondering why if(count) and set analysis is giving different output? If you see middle one output, Set analysis should output 0 but it is showing 2 as count.

Re: Set Analysis - is it case insensitive ?

MK_QSL — Sat, 03 Oct 2015 09:40:50 GMT

I don't think any problem. Can you let us know which expression is giving wrong result?

Re: Set Analysis - is it case insensitive ?

Anonymous — Sat, 03 Oct 2015 09:47:10 GMT

Digviya, Great if you could post the expressions as text, for copy and pasting. I know they are not that long, but I am feeling lazy today.

Re: Set Analysis - is it case insensitive ?

Digvijay_Singh — Sat, 03 Oct 2015 10:09:21 GMT

Expressions are -

=count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'

and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'

,[Product ID]))

=count({<[Flag Field]-={1},

[Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

'If' is generating correct results I feel but SET is not showing correct results when Field A values are changed with different case in script, please keep in mind I am not changing anything else except the script value for Field A. I am not making any change in expressions for all three cases.

Re: Set Analysis - is it case insensitive ?

PrashantSangle — Sat, 03 Oct 2015 11:07:10 GMT

Hi,

As per my understanding of Set Analysis

when you comparing a field value in Set Analysis then Set Analysis work in 2 cases:

Case 1: It will compare the field value with Case sensitve.

Case 2 : If case 1 fails then field value compare with in-case Sensitive

Regards

Re: Set Analysis - is it case insensitive ?

maxgro — Sat, 03 Oct 2015 13:07:35 GMT

Max, it seems as you said for Case1 and 2

Also

Case 3: with *, case insensitive

Re: Set Analysis - is it case insensitive ?

swuehl — Sat, 03 Oct 2015 13:16:03 GMT

There is also this discussion

Case sensitive problem in Set-Analysis

(with no solution).

hic mentioned that QV will currently not distinguish between single and double quotes in set modifier, but he considers this as a bug that may be fixed in the future.

As above shows, it's true that there is no difference between single and double quotes, but case sensitivity / insensitivity seems to be more complex than described in his answer.

Re: Set Analysis - is it case insensitive ?

hic — Sat, 03 Oct 2015 17:55:29 GMT

This is a known bug that unfortunately hasn't been fixed yet. I haven't described it in my blog posts, since it is a bug. It has nothing to do with set analysis explicitly, but exists in normal searches too.

The behaviour is the following: If there is a case sensitive perfect match, this match is shown - and nothing else is shown. This means that if you have a field value 'BLUE' and the search string is upper case, this value will be shown. But 'Blue' and 'blue' will not. Compare the two pictures below where one field value contains an asterisk, and you'll see.

HIC

Re: Set Analysis - is it case insensitive ?

Digvijay_Singh — Sun, 04 Oct 2015 07:46:29 GMT

Dear hic‌,

Thanks for your response. So I suppose it is better to use upper or lower to ensure consistency of output for string related comparisons in set analysis. I wonder why count(if()) works properly, is it not using the same algorithm for string comparisons like we use in search/set analysis?

Regards,

Digvijay

Re: Set Analysis - is it case insensitive ?

swuehl — Sun, 04 Oct 2015 15:46:14 GMT

How do you want to use upper() and lower() here in the set modifier?

I think you can get consistent results (set analysis vs. count(if()) ) when you perform a search like this:

=count({<FieldA = {"=FieldA='Blue'"} >} Flag)

Not really a nice and intuitive solution, though.

Re: Set Analysis - is it case insensitive ?

Digvijay_Singh — Mon, 05 Oct 2015 04:33:36 GMT

Right! No way I could find to transform field data to upper/lower and then compare in set analysis, it needs to be corrected during loading only.

I tried your expression and I think it seems the only way where if and set analysis started giving same results.

I am closing this thread now based on the limitations we understood and your suggested way to keep set analysis case sensitive!

Thanks to all for spending time on this.

Re: Set Analysis - is it case insensitive ?

hic — Mon, 05 Oct 2015 09:13:12 GMT

No, QlikView and Qlik Sense searches are not case sensitive.

My previous comment in this thread explains it. It is a bug that searches only sometimes (very rarely) revert to being case sensitive, but in most cases they work correctly: case insensitive.

Literal string comparisons are however case sensitive. This is why Count(If(...)) and swuehl 's solution work fine.

HIC