Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am importing data from different sources and in one of my file i am getting "chinese" character for the country like this : Italy - 意大利
This is my script, how can i "insert" the search and replace fonction in my Scripting.
LOAD Nom,
Prénom,
[Adresse e-mail],
Status,
Langue,
[Champ libre 1],
[Champ libre 2],
Country,
Date(Floor([Date de création])) as Date,
year([Date de création]) as year,
month([Date de création]) as month,
day([Date de création]) as day,
hour([Date de création]) as hour,
Time(Frac([Date de création])) as Time
Is this your country field? then try
Data:
LOAD *,
subfield(Country,'-',1) as CorrectedCountry
......
FROM YourTable;
or
Data:
LOAD *,
keepchar(Country,'abcdefghijklmnopqrstuvwxyz') as CorrectedCountry
......
FROM YourTable;
Hi,
if You want to remove those character you can use
PurgeChar()
Also there is Replace() and KeepChar() available.
Kind Regards
If you have mapping file then you can try using the APPLYMAPPING function....
Probably You can use Keepchar () functions
If your field is having only numbers then use like below
keepchar(YourField,'0123456789') as Corrected
If your field is having only text then use like below
keepchar(YourField,'abcdefghijklmnopqrstuvwxyz') as Corrected
If your field is having only text and number then use like below
keepchar(YourField,'abcdefghijklmnopqrstuvwxyz0123456789') as Corrected
or
if your Chinese characters are fixed then copy that character and use in Purgechar() function like below
purgechar(YourField,'意大利') as Corrected
Thanks for your help...as i am very new to Qlik, how can i use it in my script..i do not see really how to implement the solution for those countries in my script.
Switzerland - 瑞士 |
Italy - 意大利 |
Italy - 意大利 |
France - 法国 |
Hong Kong - 香港 |
Hong Kong - 香港 |
United Kingdom - 英国 |
United States - 美国 |
Singapore - 新加坡 |
Hong Kong - 香港 |
purgechar(Switzerland,'Switzerland - 瑞士')
Try
keepchar(Country, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz')
LOAD Nom,
Prénom,
[Adresse e-mail],
Status,
Langue,
[Champ libre 1],
[Champ libre 2],
keepchar(Country, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz') as Country,
Date(Floor([Date de création])) as Date,
year([Date de création]) as year,
month([Date de création]) as month,
day([Date de création]) as day,
hour([Date de création]) as hour,
Time(Frac([Date de création])) as Time
You may wish to add additional characters to the keepchar string if the data includes dashes or accented characters etc.
Is this your country field? then try
Data:
LOAD *,
subfield(Country,'-',1) as CorrectedCountry
......
FROM YourTable;
or
Data:
LOAD *,
keepchar(Country,'abcdefghijklmnopqrstuvwxyz') as CorrectedCountry
......
FROM YourTable;
I really like this solution. On first hand I would say to use an ApplyMap but that would push you to have another file to make the mapping, but of course this one is from someone with much more experience and wisdom. Love it!
Cheers,
Andrés
yes but i have 5 lists to import (DE; FR, ZH, ES, EN) and i do have only the challenge in the ZH list...