Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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: |
v: |
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
Try like:
Load
if(upper(Left(Location,1))='C', Location, 'OtherThanC:'&SubField(Location,':',2)) as NewLocation
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.
try
if(wildmatch(left(Location,1),'c','C'),Location,Replace('Otherthanc:'&''&subfield(Location,':',-1),Location,Location))
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
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:
v:
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
Unfortunately i have path without : also.
Sorry for confusion guys... please find the attached sample data for reference.
This will be helpful to understand the data.
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;
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