topic combining 3 function (trim(subfield(replace in QlikView

combining 3 function (trim(subfield(replace

Anonymous — Thu, 20 Oct 2011 09:02:29 GMT

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

Re: combining 3 function (trim(subfield(replace

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 09:09:28 GMT

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

Re: combining 3 function (trim(subfield(replace

Anonymous — Thu, 20 Oct 2011 09:33:40 GMT

Hi Miguel,

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

Regards

iSam

Re: combining 3 function (trim(subfield(replace

Thu, 20 Oct 2011 09:33:55 GMT

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

Re: combining 3 function (trim(subfield(replace

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 09:35:51 GMT

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