Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Parse a field

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

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Parse a field

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

];

4 Replies
swuehl
Not applicable

Re: Parse a field

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?

Not applicable

Re: Parse a field

I am not. Could that be the problem?

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

swuehl
Not applicable

Re: Parse a field

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

];

Not applicable

Re: Parse a field

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

Thanks for your help!