Find and replace the Text in the IF condition

[PBITLATMHCC]

Hi Guys, 

I need some assistance on minor code.  I have a field name called "CountryCode" consists of list of values are 'Germany-capital349' , 'Koria-capital682' etc.. 

I'm looking to write one if condition in the load statement. 

'Germany-capital349'   --replace--> Deutschland - capital349

'Koria-capital682'        replace --> Seoul - capital682   

basically the first few country charectors should be replace with  other text. Please help

CountryCode	Expected Result
Germany-capital349	Deutschland - capital349
Koria-capital682	Seoul - capital682
Malaysia-capital22	Coula - capital22

[Or]

How do you expect Qlik to make this replacement? There doesn't seem to be any particular logic to it (in the first line you've replaced the country with the local-language country name, but in the second you've replaced the country, which is not in English in the first place as that would be Korea, with its capital city). Do you have some sort of additional list to use for making this translation between values?

[jmartineze]

Hi,

you can break CountryCode in two fields with the function subfield like this:

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

subfiled(CountryCode,'-',2) as Code

and now you can replace all values as you want.

Finally with & you can concatenate it.

More or less like this...

A:

load 

CountryNew&'-'&Code as CountryCodeNew;

load

replace(replace(replace(Country,'Germany','Deutschland '),'Koria','Seoul '),'etc','etc') as CountryNew;

load

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

subfiled(CountryCode,'-',2) as Code 

from table;

[PBITLATMHCC]

I have total only 6 values in the Countrycode Field. as per requirement business provided inputs like where there is a  "Koria-capital682" replace with "Seoul - capital682".  and same as second value  "Malaysia-capital22"  first few characters replace with "Coula - capital22" 

I'm trying to implement in the transformation layer, if condition  

[Or]

This hasn't clarified the issue at all, but if there are only six total values, I would just write a basic pick/match statement such as (for the three values you provided):

Pick(Match(Countrycode ,'Germany-capital349','Koria-capital682','Malaysia-capital22'),'Deutschland - capital349','Seoul - capital682','Coula - capital22') as FieldNameHere

[MarcoWedel]

maybe helpful:

https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFun...