topic Re: Trim dedicated String in QlikView

Trim dedicated String

abhaysingh — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I have field in which values are like below

jaipur

imp. jaipur

Bearings

imp. Bearings

i want to exclude imp. from the values.. how can i achieve it

thanks

Re: Trim dedicated String

PrashantSangle — Tue, 28 Jun 2016 05:28:28 GMT

Hi,

Use replace()

try like

replace(fieldName,'imp.','')

Regards

Re: Trim dedicated String

Chanty4u — Tue, 28 Jun 2016 05:29:09 GMT

try this

ss:

LOAD * INLINE [

naem

jaipur

imp. jaipur

Bearings

imp. Bearings

];

Main:

LOAD * ,

PurgeChar(naem,'imp.') as new

Resident ss;

Re: Trim dedicated String

danansell42 — Tue, 28 Jun 2016 05:37:21 GMT

Hi Abhay

The answer from Max will work or you could also use the substring command.

Wouldn't use purgechar as it will also drop any other i,m or p's from the text

Thanks

Dan

Re: Trim dedicated String

avinashelite — Tue, 28 Jun 2016 05:41:12 GMT

Try with Replace()

replace(Field,'imp.','')

Re: Trim dedicated String

avinashelite — Tue, 28 Jun 2016 05:43:15 GMT

sureshqv‌ we should not use Purgechar() in this case it will eliminate all the words with letter i, m , p and . You could see the same in your example where jaipur has become jaur

Re: Trim dedicated String

maniram23 — Tue, 28 Jun 2016 05:43:33 GMT

Hi,

May be it will full for you.

T1:

load * Inline [

ABC

jaipur

imp. jaipur

Bearings

imp. Bearings

];

LOAD *,

LTrim(Replace(ABC,'imp.','')) as new

Resident T1;

DROP Table T1;

Re: Trim dedicated String

qlikview979 — Tue, 28 Jun 2016 05:49:07 GMT

Hi Try this,

T1:

LOAD * INLINE [

name

jaipur

imp. jaipur

Bearings

imp. Bearings

];

T2:

LOAD * ,

"Replace(name,'imp.','') as replace

Resident T1;

Re: Trim dedicated String

MindaugasBacius — Tue, 28 Jun 2016 05:49:55 GMT

Apart from what is already said:

load *
  ,if(left(Field, 4) = 'imp.', trim(SubField(Field, 'imp.', 2)), trim(Field)) as Field2
  ,if(left(Field, 4) = 'imp.', trim(right(Field, len(Field) - 4)), trim(Field)) as Field3
inline [
Field
jaipur
imp. jaipur
Bearings
imp. Bearings
];

Result:

Check the attachment.

Re: Trim dedicated String

Chanty4u — Tue, 28 Jun 2016 06:03:24 GMT

thnx avi fro ur clarification

Re: Trim dedicated String

abhaysingh — Tue, 28 Jun 2016 06:08:30 GMT

Thanks for help,

I this if i want to replace two values simultaneously than?

say e.g.

box/stell

i want to show only Box as output?

thanks in advance

Re: Trim dedicated String

avinashelite — Tue, 28 Jun 2016 06:11:03 GMT

if you have all the values as / separate than then try with subfield(field_name,'/',1) as new_field

Re: Trim dedicated String

PrashantSangle — Tue, 28 Jun 2016 06:15:22 GMT

Hi,

try like

if(match(fieldName,'imp.'),replace(fieldName,'imp.',''),if(match(fieldName,'/stell'),replace(fieldName,'/stell','')))

if you have all the values as / separate than then try what avinash suggested.

Regards