How to Make QlikView Ignore Case Sensitivity of Data?

[kanhomcake]

hi all,

Does anyone know how to make QlikView treat data as case insensitive? Because, I would like to join 2 tables (example below) using LOAD JOIN statements. By default, data in these two tables (Country in Table1 and Country in Table2) will not match each other because of case sensitivity in QlikView unless I use Force statement like force ( capitalization | case upper | case lower ) but I want to keep the character case remaining exactly as it is in each table (Capitalized in Table1 and ALL CAPITAL LETTER in Table2 for column Country). So Force statement doesn't help in my case. I also know that there are functions like upper() and lower() which don't help neither if I want to keep the character case. So I think there should be some configuration parameter in QlikView environment to switch on/off to make QlikView treat data either to be case sentive or insensitive.

Table1:

Country	ColumnA
Egypt	1
China	2
Brazil	3
Thailand	4
Iran	5

Table2:

Country	ColumnB
EGYPT	a
CHINA	b
BRAZIL	c
THAILAND	d
IRAN	e

Thank you so much in advance for anyone who can give me even a small clue of how to do so.

Best regards,

[fdelacal]

yoou can do it, i don´t know if is ok for you

table1:

load

upper(Country) as CountryKey,

Country as CountryTab1,

ColumnA

from (your route);

join load

Country as CountryKey

Country as CountryTab2,

ColumnB

from (your route);

Country

Count

y

[jonathandienst]

Hi

Similar to the prevoius post:

//--- Load table 1
Table1:
LOAD Country,
          lower(Country) As lCountry,
          ColumnA
Inline
[
          Country, ColumnA
          Egypt, 1
          China, 2
          Brazil, 3
          Thailand, 4
          Iran, 5
];
//--- Load table 2
Table2:
LOAD *
Inline
[
          Country          ColumnB
          EGYPT, a
          CHINA, b
          BRAZIL, c
          THAILAND, d
          IRAN, e
];
//--- join the tables
Join(Table1)
LOAD lower(Country) As lCountry,
          ColumnB
Resident Table2;
//--- clean up
DROP Field lCountry;
DROP Table Table2;

Hope that helps

Jonathan

[kanhomcake]

thank you for your reply, Facundo de la cal it helps as the interim solution but in the long run, if I have hundred tables from multiple data sources, this approach is not convenient at all. It's a time consuming task on the unnecessary job, in my opinion anyway, thanks again for your reply

[kanhomcake]

hi Jonathan Dienst, thank you for your reply Yes, it helps as the interim solution but i'm looking for the long run solution, to handle my multiple data sources which I never know in what format they will come. Thanks again anyway

[carahyba]

I think the best way is to use Capitalize(Country) as Country. AAAA become Aaaa.