Case insensetive association

[m4u]

Is there any way to make QV associate data case insansetive? I base my report on a case insensetive sql db with lots of nvarchar keys, which are not always in-sync case wise. I know I can go manually and UPPER() them, but is there some kind of way to do it with a single command on a script level or any optiion in the software itself?

[prieper]

In the script you can force QV to use only upper-case-letters, the command is:

FORCE CASE UPPER;

Check the OL-Help for lower case or mixed.

HTH
Peter

[m4u]

Hi

This doesn't work for me - it gives errors in other unrelated places in the script. I've digged a little and found this post

http://community.qlik.com/forums/p/23713/90612.aspx#90612

from which I understand this works on field names rather then values

When I spoke of case insensetive association, I meant field values - for instance, if two tables have a field Customer and one has the value 'John' and the other is 'john', I want QV to link them..

[prieper]

On individual fields you may use UPPER, e.g.

LOAD UPPER(Customer) AS Customer, ....

Peter

[johnw · Accepted solution]

M4U wrote:if two tables have a field Customer and one has the value 'John' and the other is 'john', I want QV to link them..

QlikView doesn't and I believe shouldn't do case-insensitive association. Consider your example, field Customer with values 'John' and 'john'. If these are to be considered the SAME value, then how should they be displayed in charts, list boxes and so on? QlikView isn't going to guess that you want, for instance, for it to be displayed as 'John'. You have to tell it. To tell it how you want to see this "one" value, you need to change all occurrences of 'john' to 'John'. And at that point, QlikView will recognize that there is only one customer here, 'John', so there's no funny associations to be made.

Cleaning up names can get arbitrarily complex, depending on your data source. Sometimes a simple capitalize(Customer) would be enough. But here's an example from one of my applications:

[Person Number Map]:
MAPPING LOAD
capitalize(trim(text("Payroll Number"))) as "Number"
,trim(subfield("Employee",'(',1)) as "Person"
RESIDENT [Employees]
;
[Contacts2]:
LOAD *
,applymap('Person Number Map',
capitalize(trim(text(
if(left("CB",3)='CN='
,subfield(mid("CB",4),'/',1)
,trim(subfield(subfield(subfield("CB",',',2),':',1),' ',1)) & ' '
& trim(subfield("CB",',',1))))))) as "Contacted By"

[m4u]

OK

I understand what you're saying about QV cannot guess how to display this properly.

In my case though, I'm speaking of internal key fields which are not going to be displayed anywhere. I guess I'll just have to treat it manually while loading the data.

Thanks