Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Column Problem

If there is a column with location. all locations starts with "  SL. " now i want to remove this SL. but the thing is some of them dont have this so the right and mid comand is actualy cutting the first 3 letter so anything we can do for only selcting SL. and deleting the SL. only from SL.Mumbai and etc.

Location name

SL.Mumbai

SL.Gurgaon

Latur                 Here Lat is cutted if right or mid is used

SL.Chennai

Tags (2)
1 Solution

Accepted Solutions
qlikview2015
Contributor

Re: Column Problem

Hi !

Try this....

if(left([Location Name],2)='SL',mid([Location Name],4,15),[Location Name])

Best regards

Mike Preuss

6 Replies
stigchel
Honored Contributor

Re: Column Problem

You can use an if statement, something like

If(Left([Location name],3)='SL.',Mid([Location name],4,len([Location name])),[Location name])

MVP
MVP

Re: Column Problem

if(left(location,2)='SL', right(location, len(location)-3), location) as location

robert99
Valued Contributor II

Re: Column Problem

Try using Index

if (Index( FIELDNAME,'SL.') = 1 ,ETC

or replace might work

replace(fieldname , 'SL.','')

you might need trim (replace(fieldname , 'SL.',' ') )

or maybe

replace(fieldname , 'SL.',null())

qlikview2015
Contributor

Re: Column Problem

Hi !

Try this....

if(left([Location Name],2)='SL',mid([Location Name],4,15),[Location Name])

Best regards

Mike Preuss

Not applicable

Re: Column Problem

Thank you bro.. !

qlikview2015
Contributor

Re: Column Problem

Hi !

If there are Location Names that are longer than 15 characters, the number must be bigger...

Best regards

Mike Preuss