Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
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.
BI Consultant
Hi Miguel,
THANKS A LOT!!!!! That worked!!! Just curious how did you decide which function should be used first?
Regards
iSam
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 IMEXRESIDENT A;
Thanks,
James
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.
BI Consultant