combining 3 function (trim(subfield(replace

[Anonymous]

Hi Guys,

I have a field that needs to be extract in to 2 fields.

I've used subfield function to extract 1 field into 2 fields 'country' and 'imex' then I used the trim function to remove unnessary spaces within the field imex. Now I want to add the replace function to change some fieldvalues. My script now looks:

if(trim(subfield(Fieldvalue, ',', 2)),

       if(replace(Fieldvalue,'A/','A'),

       If(replace(Fieldvalue, 'VL','V'),

       if(replace(Fieldvalue, 'EP', 'E'))))) as imex,

However this is incorrect. I don't know how to combine these 3 function into one formula. if you combine multiple function, how do you know which one you should use first. In my case, should I start with: the replace function then the trim and subfield? or first the trim(subfield then the replace function?

Hope u can help!

Cheers!

Isam

[Miguel_Angel_Baeyens · Accepted solution]

Hi Isam,

I may be wrong but I don't think you need so many If()s, rather than nesting Replace():

Trim(Replace(Replace(Replace(SubField(Fieldvalue, ',', 2), 'A/', 'A'), 'VL', 'V'), 'EP', 'E')) AS imex

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

Hi Miguel,

THANKS A LOT!!!!! That worked!!! Just curious how did you decide which function should be used first?

Regards

iSam

[#4]

Hi Isam,

I'm not sure you need the if statements. As you have multiple scenarios where you are wishing to replace values, I would use the mapsubstring function and create a map with all the values you wish to replace.

The mapsubstring function then goes around the subfield and trim functions.

Map:
Mapping LOAD * INLINE [
RemoveString, ReplaceString
A/,A
EP,E
VL,V
];
A:
LOAD * INLINE [
CountryImex
France;CA/L ;123
Germany;VLVATY;321
Spain; EPRAM;456
];

B:
LOAD
mapsubstring('Map',trim(subfield(CountryImex,';',2))) AS IMEX
RESIDENT A;

Thanks,

James

[Miguel_Angel_Baeyens]

Hi Isam,

First should be SubField(), since you need to get the value from the greater part. Second each of the replaces, then the Trim(). You can do the Trim() then the Replace(), but always first the SubField().

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica