topic Re: Trimming extra details after third character instance in QlikView

Trimming extra details after third character instance

dawgfather — Wed, 25 Nov 2015 15:23:51 GMT

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/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.

Re: Trimming extra details after third character instance

sunny_talwar — Wed, 25 Nov 2015 16:27:33 GMT

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

];

Re: Trimming extra details after third character instance

swuehl — Wed, 25 Nov 2015 16:28:04 GMT

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

];

Re: Trimming extra details after third character instance

dawgfather — Wed, 25 Nov 2015 20:30:30 GMT

Worked like a charm! Thank you very much.