topic Re: transforming data in QlikView

transforming data

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: transforming data

PrashantSangle — Tue, 10 May 2016 07:40:39 GMT

Hi,

if You want to remove those character you can use

PurgeChar()

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

Kind Regards

Re: transforming data

avinashelite — Tue, 10 May 2016 07:41:28 GMT

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

quick applymap example

Re: transforming data

Kushal_Chawda — Tue, 10 May 2016 07:45:37 GMT

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

if your Chinese characters are fixed then copy that character and use in Purgechar() function like below

purgechar(YourField,'意大利') as Corrected

Re: transforming data

Tue, 10 May 2016 08:19:37 GMT

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 - 意大利

France - 法国

Hong Kong - 香港

United Kingdom - 英国

United States - 美国

Singapore - 新加坡

Hong Kong - 香港

Re: transforming data

Tue, 10 May 2016 08:21:05 GMT

purgechar(Switzerland,'Switzerland - 瑞士')

Re: transforming data

Colin-Albert — Tue, 10 May 2016 08:28:31 GMT

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.

Re: transforming data

Kushal_Chawda — Tue, 10 May 2016 08:30:19 GMT

Is this your country field? then try

Data:

LOAD *,

subfield(Country,'-',1) as CorrectedCountry

......

FROM YourTable;

Data:

LOAD *,

keepchar(Country,'abcdefghijklmnopqrstuvwxyz') as CorrectedCountry

......

FROM YourTable;

Re: transforming data

andrespa — Tue, 10 May 2016 08:46:51 GMT

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

Re: transforming data

Tue, 10 May 2016 08:47:13 GMT

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

Re: transforming data

Kushal_Chawda — Tue, 10 May 2016 08:51:46 GMT

what are these lists? what challenges you have in ZH?

Re: transforming data

Colin-Albert — Tue, 10 May 2016 08:52:34 GMT

keepchar(Country,'abcdefghijklmnopqrstuvwxyz') as CorrectedCountry

This expression will remove all uppercase characters from the Country field!

Re: transforming data

Kushal_Chawda — Tue, 10 May 2016 08:54:24 GMT

then probably this

keepchar(lower(Country),'abcdefghijklmnopqrstuvwxyz') as CorrectedCountry

Re: transforming data

avinashelite — Tue, 10 May 2016 09:03:03 GMT

Hi Jean,

Do you want to remove the chines character or do you need to replace it with any other character set ??

Re: transforming data

Anonymous — Tue, 10 May 2016 09:41:13 GMT

PUGRE CHAR OR KEEP CHAR IS USEFUL TO YOU

Re: transforming data

tamilarasu — Tue, 10 May 2016 10:00:00 GMT

If you have a list of countries to replace, you can use apply map method as mentioned by Avinash.

Re: transforming data

engishfaque — Tue, 10 May 2016 15:13:19 GMT

Dear Jean,

Kindly find attached app. I hope this is the simplest one solution.

Kind regards,

Ishfaque Ahmed

Re: transforming data

Wed, 11 May 2016 06:46:33 GMT

THanks for the question, i need to replace Germany - 德国 with Germany

But i need to do this for 10 different countries

Re: transforming data

PrashantSangle — Wed, 11 May 2016 07:44:48 GMT

Hi,

If pattern of your country name is always like

countryName - 德国

then you can use subfield() also

try like subfield(CountryName,'-')

For details always search in help menu. You will get better idea.

Kind Regards

Re: transforming data

Wed, 11 May 2016 08:01:30 GMT

sub works perfectly ....i did think about it thanks