Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have some SQL tables that has data that includes (but might not) a string of characters that has multiple forward slashes. Think of them as similar to folders and subfolders. I am trying to determine a way to trim off the excess after the third "/" - if there is a third - and leave the base details. Essentially I want to see data two folder levels down, but no further. Sample data would include something like the below and then what I am trying to get to below that.
Sample data:
Servername0
Servername1:/vol
Servername2:/vol/folder1/folder2/folder3
Servername2:/vol/folder1/folder3/folder4
Servername2:/vol/folder1/folder5
Servername2:/vol/folder6/folder7/folder8/folder9
Data looking for:
Servername0
Servername1:/vol
Servername2:/vol/folder1
Servername2:/vol/folder1
Servername2:/vol/folder1
Servername2:/vol/folder6
Anyone have some fancy coding to accomplish this?
I can do some CHARSET stuff, but then the lines without a "/" get messed up. I can do it all in Excel by doing text to columns and then concatenating selected columns back together (excluding extra columns), but that is overlay painful.
Or maybe
LOAD * ,
Left(Sample, If(Index(Sample,'/',3), Index(Sample,'/',3)-1,Len(Sample))) as Result
INLINE [
Sample
Servername0
Servername1:/vol
Servername2:/vol/folder1/folder2/folder3
Servername2:/vol/folder1/folder3/folder4
Servername2:/vol/folder1/folder5
Servername2:/vol/folder6/folder7/folder8/folder9
];
May be this in QlikView Script:
Table:
LOAD *,
SubField(SampleData, '/', 1) & If(Len(KeepChar(SampleData, '/')) >= 1, '/') & SubField(SampleData, '/', 2) & If(Len(KeepChar(SampleData, '/')) >= 2, '/') & SubField(SampleData, '/', 3) as Solution;
LOAD * Inline [
SampleData
Servername0
Servername1:/vol
Servername2:/vol/folder1/folder2/folder3
Servername2:/vol/folder1/folder3/folder4
Servername2:/vol/folder1/folder5
Servername2:/vol/folder6/folder7/folder8/folder9
];
Or maybe
LOAD * ,
Left(Sample, If(Index(Sample,'/',3), Index(Sample,'/',3)-1,Len(Sample))) as Result
INLINE [
Sample
Servername0
Servername1:/vol
Servername2:/vol/folder1/folder2/folder3
Servername2:/vol/folder1/folder3/folder4
Servername2:/vol/folder1/folder5
Servername2:/vol/folder6/folder7/folder8/folder9
];
Worked like a charm! Thank you very much.