Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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
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
I think the best way is to use Capitalize(Country) as Country. AAAA become Aaaa.