Discussion Board for collaboration on QlikView Scripting.
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..
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.
Go to Solution.
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.
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 [
Subfield should return you the parts of your string:
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;
Are you always coping with same depth of your hierarchy?
I am not. Could that be the problem?
Some are only two folders deep, some may be 4 or 5.
I was putting the subfield into the wrong resident load in my script. So this works.
Thanks for your help!