3 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Multiple Report Lines to Multiple Table Columns

    Karen O

      Hello,

      I am exporting to an excel document.

       

      Report example is as follows:

       

      fld1 -- fld2 -- fld3 --  fld4 -- fld5

      222 --  descr -   555 --   aaa --  bbb

      -


      435

      -


      482

       

        Fld3 has a variable number of lines (anywhere from 1 to 5, and sometimes it is blank) - I want each line of fld3 to translate to a separate column in excel with all the other fields.

       

      (fld1-- fld2 -- fld4-- fld5-- fld3ln1-- fld3ln2 fldln3...)

      222 - descr-- aaa--- bbb - 555-  435---  482

       

        It kind of works if my detail line contains multiple lines, but not quite.  I've tried making fld3 my detail.  Couldn't get it to work right, either.  I believe I've done this before in an earlier version.  (I'm on V7 now.)

      (Basically, extracting an address block does the same thing I want to do, only without addresses.)

       

      I hope someone can help.

       

      Thanks,

       

      [size="1"][ December 17, 2004, 06:54 PM: Message edited by: Karen O ][/size]

        • Multiple Report Lines to Multiple Table Columns
          Grant Perkins

          Karen,

           

          Sounds like the easiest way would be to extract fld3 as a multi-line field then create 5 (if 5 is the maxium number of lines possible) calculated fields and SPLIT (LSPLIT or RSPLIT) the extracted field into those.

           

          That said the contents of the lines may make the split a little tricky but if they are 3 character codes as per the sample it should be quite straightforward.

           

          Hide the original fld3 field for the final output.

           

          If there is more to it than that let us know.

           

          Grant

           

           

          Originally posted by Karen O:

          Hello,

          I am exporting to an excel document.

           

          Report example is as follows:

           

          fld1 -- fld2 -- fld3 --  fld4 -- fld5

          222 --  descr -   555 --   aaa --  bbb

          -


          435

          -


          482

           

            Fld3 has a variable number of lines (anywhere from 1 to 5, and sometimes it is blank) - I want each line of fld3 to translate to a separate column in excel with all the other fields.

           

          (fld1-- fld2 -- fld4-- fld5-- fld3ln1-- fld3ln2 fldln3...)

          222 - descr-- aaa--- bbb - 555-  435---  482

           

            It kind of works if my detail line contains multiple lines, but not quite.  I've tried making fld3 my detail.  Couldn't get it to work right, either.  I believe I've done this before in an earlier version.  (I'm on V7 now.)

          (Basically, extracting an address block does the same thing I want to do, only without addresses.)

           

          I hope someone can help.

           

          Thanks, /b[/quote]

          • Multiple Report Lines to Multiple Table Columns
            Karen O

            Thanks, that may work.  I WAS getting multiple info concatenated together in one field.  Didn't know you could split like that.  The fields are 5 numeric in length.

             

            KO

            • Multiple Report Lines to Multiple Table Columns
              Grant Perkins

              OK Fields in blocks of 5 characters separated by spaces should be no problem, just use the space as the separator character. There is just a chance that you may end up with a gap between 2 numbers which is of two or more spaces and that can throw the results, mainly because you may not spot the double space. If that happens you need to use the TRIM or LTRIM functions perhaps.

               

              LSPLIT(<FIELD>,5," ",1)

               

              would give the first part of the 5 'line' field.

               

              LSPLIT(<FIELD>,5," ",2)

               

              would give the second and so on.

               

              Good luck.

               

               

              Grant

               

              Originally posted by Karen O:

              Thanks, that may work.  I WAS getting multiple info concatenated together in one field.  Didn't know you could split like that.  The fields are 5 numeric in length.

               

              KO /b[/quote]