Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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?
I am not. Could that be the problem?
Some are only two folders deep, some may be 4 or 5.
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
];
I was putting the subfield into the wrong resident load in my script. So this works.
Thanks for your help!