5 Replies Latest reply: Nov 7, 2012 1:10 AM by sujeet Singh RSS

    Seperate delimited data

    ali ceyli

      Hi everyone,

      In my access database, there is fiield showing employee's identiiy but it ıncludes more than one id with ";"

      such as 100;101

      İn qv side 100 id ' s name Alex but because of delimited data ı can not see the name of 100 ID

       

      ID                         Name

      100;101               ------

      100                         Alex

      101                          David

       

      How can I seperate the delimited field in two different rows to see names?

        • Re: Seperate delimited data

          Hi,

           

          You can use subfield function.

           

          subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

           

          Regards,

           

          Janzen

          • Re: Seperate delimited data
            sivaraj seeman

            Try by loading in script

             

            load *, subfield(fieldname,';') as new_field

              • Re: Seperate delimited data
                ali ceyli

                Hello

                Your solution makes delimited fields into one column I wanna explain in an example

                 

                In my qw

                ID                         Name              

                100                        Alex

                100;101                    --

                102                         David

                 

                After your solution 100;101 fields are seperated but I wanna it in one column so ı can see the name of ID

                  • Re: Seperate delimited data
                    sivaraj seeman

                    a:

                    Mapping

                    LOAD * Inline [

                        new_id, Name

                        100,  Alex

                        101,  David

                         

                      ];   

                     

                    b:

                    LOAD *, SubField(ID_Name,';')as new_id Inline [

                    ID_Name , Name

                     

                    100;101 ,------

                    100,  Alex

                    101,  David

                    ];

                    c:

                    NoConcatenate

                    LOAD *, ApplyMap('a',new_id) as name

                    Resident b;

                    drop Table b;

                     

                    In actual you have many ID for that you can replace table a with excel sheet that have id and name

                      • Re: Seperate delimited data
                        sujeet Singh

                        Hi,

                        Just try this may be it help you....

                        1. if its sure that Your ID is of fixed lenght '3'

                          Then use this -----

                         

                         

                           =  if(len(ID_Name)=3,ID_Name,subfield(ID_Name,';'))

                           

                        Else if it is not so Then,

                         

                         

                            =if(substringcount(ID_Name,';')=0,ID_Name,subfield(ID_Name, ';'))

                         

                         

                        Else give me sample file.....