4 Replies Latest reply: Oct 2, 2012 11:08 AM by Ethan Beaulieu RSS

    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

        • Re: Parse a field
          Stefan Wühl

          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

              I am not. Could that be the problem?

               

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

                • Re: Parse a field
                  Stefan Wühl

                  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

                  ];