Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

aboumejjane
Contributor

combining 3 function (trim(subfield(replace

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

1 Solution

Accepted Solutions
MVP
MVP

Re: combining 3 function (trim(subfield(replace

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

4 Replies
MVP
MVP

Re: combining 3 function (trim(subfield(replace

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

Highlighted
aboumejjane
Contributor

Re: combining 3 function (trim(subfield(replace

Hi Miguel,

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

Regards

iSam

Not applicable

Re: combining 3 function (trim(subfield(replace

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

MVP
MVP

Re: combining 3 function (trim(subfield(replace

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