Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

kanhomcake
New Contributor III

How to Make QlikView Ignore Case Sensitivity of Data?

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:

CountryColumnA
Egypt1
China2
Brazil3
Thailand4
Iran5

Table2:

CountryColumnB
EGYPTa
CHINAb
BRAZILc
THAILANDd
IRANe

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

Best regards,

5 Replies
fdelacal
Valued Contributor

Re: How to Make QlikView Ignore Case Sensitivity of Data?

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

MVP
MVP

Re: How to Make QlikView Ignore Case Sensitivity of Data?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kanhomcake
New Contributor III

Re: How to Make QlikView Ignore Case Sensitivity of Data?

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
New Contributor III

Re: How to Make QlikView Ignore Case Sensitivity of Data?

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
New Contributor II

Re: How to Make QlikView Ignore Case Sensitivity of Data?

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