transforming data

[#1]

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

[PrashantSangle]

Hi,

if You want to remove those character you can use

PurgeChar()

Also there is Replace() and KeepChar() available.

Kind Regards

[avinashelite]

If you have mapping file then you can try using the APPLYMAPPING function....

quick applymap example

[Kushal_Chawda]

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

[#5]

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 - 香港

[#6]

purgechar(Switzerland,'Switzerland - 瑞士')

[Colin-Albert]

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.

[Kushal_Chawda · Accepted solution]

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;

[andrespa]

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

[#10]

yes but i have 5 lists to import (DE; FR, ZH, ES, EN) and i do have only the challenge in the ZH list...