topic Re: Parse a field in QlikView

Parse a field

Tue, 02 Oct 2012 14:23:01 GMT

So I have a field, we'll call it Detail.

I need to separate the field into different parts. The field contains a folder structure. So it may look something like this..

/Folder1/Folder2/Folder3/Folder4/FileName/

I want to separate each part of the structure into its own field so I can choose them individually.

I already tried the subfield function like this...

subfield(Detail,'/',1) as Detail_Test1

This seems to work to bring the last part of the folder structure back. But I'm unsure how to get the rest. I tried changing 1 to 2,3,etc but it doesn't show me the correct data.

Thanks

Re: Parse a field

swuehl — Tue, 02 Oct 2012 14:36:41 GMT

Subfield should return you the parts of your string:

LOAD *,

SubField(Detail,'/',2) as Test1,

SubField(Detail,'/',3) as Test2,

SubField(Detail,'/',4) as Test3,

SubField(Detail,'/',5) as Test4,

SubField(Detail,'/',6) as Test5;

LOAD * INLINE [

Detail

/Folder1/Folder2/Folder3/Folder4/FileName/

];

Are you always coping with same depth of your hierarchy?

Re: Parse a field

Tue, 02 Oct 2012 14:40:50 GMT

I am not. Could that be the problem?

Some are only two folders deep, some may be 4 or 5.

Re: Parse a field

swuehl — Tue, 02 Oct 2012 14:50:21 GMT

I assume you need to parse the file name into a certain field, and then the parts of your hierarchy in other fields.

You can use negative indices with subfield to access the partial strings starting from the back.

Maybe like

LOAD *,

SubField(Detail,'/',-1) as Filename_Test,

If(substringcount(Detail,'/')>1,SubField(Detail,'/',2)) as Folder1,

If(substringcount(Detail,'/')>2,SubField(Detail,'/',3)) as Folder2,

If(substringcount(Detail,'/')>3,SubField(Detail,'/',4)) as Folder3,

If(substringcount(Detail,'/')>4,SubField(Detail,'/',5)) as Folder4,

If(substringcount(Detail,'/')>5,SubField(Detail,'/',6)) as Folder5;

LOAD * INLINE [

Detail

/Folder1/Folder2/Folder3/Folder4/FileName

/Folder1b/Folder2b/FileNameb

];

Re: Parse a field

Tue, 02 Oct 2012 15:08:07 GMT

I was putting the subfield into the wrong resident load in my script. So this works.

Thanks for your help!