topic Replacing some text in field in QlikView

Replacing some text in field

raju_salmon — Wed, 16 Nov 2016 08:40:51 GMT

Hi,

Please help to get result for below scenario. I have field called location where some paths are available. I need to keep all paths started with C: or c: as it is and remaining drives i need to replace with 'OtherThanC'

EX: Original values - f:\link\linkng630

Result should be - OtherThanC:\link\linkng630

Location

U:\Users\Center\appdata\roaming\microsoft\windows\start menu\programs\startup

u:\users\center\appdata\roaming\microsoft\windows\start menu\programs\startup

v:\program files

v:\program files\dbs

f:\link\linkng630

f:\link\linkng630\lng

D:\program files (x86)

d:\program files (x86)

c:\windows\syswow64\adobe\director

C:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\svg viewer

c:\windows\syswow64\adobe\svg viewer 3.0

I have tried using replace, subfield functions but cannot able to get this result. This is sample data and path could be anything irrespective of depth of path.

Can anyone help me how to create new field using 'Location' field to get accurate result?

Thanks alot.

Raju

Re: Replacing some text in field

tresB — Wed, 16 Nov 2016 08:51:51 GMT

Try like:

Load

if(upper(Left(Location,1))='C', Location, 'OtherThanC:'&SubField(Location,':',2)) as NewLocation

Re: Replacing some text in field

rubenmarin — Wed, 16 Nov 2016 08:53:06 GMT

Hi Raju, every row starts with a letter and a colon?, in that case;

'OtherThanC' & Mid(Location, Index(Location, ':'))

You can add a check to keep the lowercased c:

Edit: I first wrong-readed convert to 'C', if the text is OtherThanC, the check is neccesary, use the option given by tresesco.

I used mid in case there will be another colon in the string, but if this are paths colons are not allowed so there is no need to consider.

Re: Replacing some text in field

shraddha_g — Wed, 16 Nov 2016 08:55:18 GMT

try

if(wildmatch(left(Location,1),'c','C'),Location,Replace('Otherthanc:'&''&subfield(Location,':',-1),Location,Location))

Re: Replacing some text in field

raju_salmon — Wed, 16 Nov 2016 09:00:56 GMT

Thanks Tresesco,

I have two issues here.

One is my path may start more than one letter drive like

ADE:\windows\syswow64\adobe\svg viewer

Tres:\windows\syswow64\adobe\svg viewer 3.0

second one is, if i concatenate only &SubField(Location,':',2)how i can get whole path? Please help me

Re: Replacing some text in field

its_anandrjs — Wed, 16 Nov 2016 09:03:09 GMT

Try with this way

Data:

LOAD * Inline

[

Location

U:\Users\Center\appdata\roaming\microsoft\windows\start menu\programs\startup

u:\users\center\appdata\roaming\microsoft\windows\start menu\programs\startup

v:\program files

v:\program files\dbs

f:\link\linkng630

f:\link\linkng630\lng

D:\program files (x86)

d:\program files (x86)

c:\windows\syswow64\adobe\director

C:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\svg viewer

c:\windows\syswow64\adobe\svg viewer 3.0

];

LOAD *,

left(Location,1),

If( Match(left(Trim(Location),1),'C') or Match(left(Trim(Location),1),'c'),Location, Replace(Location,left(Trim(Location),1),'OtherThanC')) as Status

Resident Data;

DROP Table Data;

Regards

Anand

Re: Replacing some text in field

raju_salmon — Wed, 16 Nov 2016 09:05:54 GMT

Unfortunately i have path without : also.

Re: Replacing some text in field

raju_salmon — Wed, 16 Nov 2016 09:11:25 GMT

Sorry for confusion guys... please find the attached sample data for reference.

This will be helpful to understand the data.

Re: Replacing some text in field

tamilarasu — Wed, 16 Nov 2016 09:46:39 GMT

Hi Raju,

Try this,

Data:

Load *,

If(Left(Location,2)<>'\\' and Upper(SubField(Location,'\',1))<>'C:',Replace(Location,SubField(Location,'\',1), 'OtherThanC:'),Location) as New_Location

From Source;

Re: Replacing some text in field

raju_salmon — Wed, 16 Nov 2016 10:54:41 GMT

Thanks Nagaraj,

Can you please tell me do we have any function to take out last folder/file name out of path

C:\windows\syswow64\adobe\abc.xls

c:\windows\syswow64\adobe\shockwave 12\abc.doc

Re: Replacing some text in field

rubenmarin — Wed, 16 Nov 2016 11:04:05 GMT

Hi Raju, also with the Subfield function:

subfield(Location, '\', -1)