5 Replies Latest reply: Aug 26, 2011 4:57 PM by Miguel Angel Baeyens de Arce RSS

    Segment data

      Hi All,

      I have a problem

      I want to segment my data and i want to know how i can do it.

      This is what i have

      CleTab   CodeSoc  CodeTable    CodeUsi     Data

      K05CR     036         32               1                331903331953331703331303321503331103331153331203

      K05DB     036         32               1                603190603195603170603130603150603110603115603120

      K06CR     036         32               1                331903331953331703331303321503331103331153331203

      K06DB     036         32               1                603190603195603170603130603150603110603115603120 

       

      For the field data, i want to separe it in 8 parts of 6 character.

       

      See below whant i want to have

      CleTab    CodeSoc    CodeTable  CodeUsi     Data

      K05CR    036            32             1               331903

      K05CR    036            32             1               331953

      K05CR    036            32             1               331703

      K05CR    036            32             1               331303

      K05CR    036            32             1               321503

      K05CR    036            32             1               331103

      ...

       

      You have in attached the file

       

      Thanks

       

      Jeff

        • Segment data
          Shubhasheesh Chakraverty

          Hi,

          Use left() to take few chars from left for data field, furthor use num() on that to encode in numeric.

           

          like num ( left ( data, 5 ) ) as newdata,

           

          HTH

          • Re: Segment data
            Stefan Wühl

            Hi,

             

            use a while in your load script together with mid() function to parse in the segments.

             

            See attached sample app.

             

            Regards,

            Stefan

            • Re: Segment data
              Miguel Angel Baeyens de Arce

              Hi there,

               

              Although Stefan's solution seems the cleanest, here's another attempt to loop the table and get the records. I haven't tested it, but since it took me some time to write it, here it goes

               

              Original:
              LOAD CleTab AS Orig_CleTab, 
                   CodeSoc AS Orig_CodeSoc, 
                   CodeTable AS Orig_CodeTable, 
                   CodeUsi AS Orig_CodeUsi, 
                   Data AS Orig_Data
              FROM
              [Table à faire.xls]
              (biff, embedded labels, table is Sheet1$);
              
              FOR i = 1 TO 48 STEP 6
                  Processed:
                  LOAD RecNo() AS No,
                       Orig_CleTab AS CleTab,
                       Orig_CodeSoc AS Code,
                       Orig_CodeTable AS CodeTable,
                       Orig_CodeUsi AS CodeUsi,
                       Mid(Orig_Data, $(i), 6) AS Data
                  RESIDENT Original;
              NEXT
              

               

              Hope that helps.

               

              Miguel Angel Baeyens

              BI Consultant

              Comex Grupo Ibérica

               

              For loop code checked according to Stefan's post suggestions

                • Segment data
                  Stefan Wühl

                  Hi Miguel,

                   

                  do you have experience what is performing better, a while or for-loop?

                   

                  I would guess it doesn' really matter on a resident table, but maybe when loading from an external data source (because the while doesn't need a repeating query, where the for-loop performs repeating table loads, if not optimized by QV)?

                   

                  (To be equivalent with my while-approach, I think you could remove the outer for-loop and you should increase the upper limit in the inner loop from 42 to 48, but that's just for the record).

                   

                  All, have a nice day,

                  Stefan

                    • Re: Segment data
                      Miguel Angel Baeyens de Arce

                      Hi Stefan,

                       

                      Actually I haven't tested this case in particular, because both have pros and cons. But on my general experience, the for next loop is usually slower, because is a load with all the transformation (that is very similar to the one in the while loop) but it concatenates in each step, and it adds extra time to the load process that the while does not.

                       

                      I will try both with several million records in the same computer and get back with the results.

                       

                      For the record, you're right, and so I correct the code, just in case. Thanks for noting, (that's the way it goes when I don't test before posting).

                       

                      Miguel Angel Baeyens

                      BI Consultant

                      Comex Grupo Ibérica