5 Replies Latest reply: May 15, 2014 10:10 AM by Grant Perkins RSS

    How to split into different columns

    rdbabu _

      Hi,

       

      I would like to bring the below as mentioend below:

       

      inv#       track#  comp name#       balance# comments1         comemnts2     comments3 comments4

      7681774  ab4578 ab1254g,lt compy 197894   balance not paid  issue in qualitynote

      Inv No      Track#                         COMP NAME

      7681774   ab4578                        AB1254G,LT COMPANY

                                         Balance: 197894

                (02/02/2011)             ***BALANCE NOT PAID    ***

                                               **ISSUE IN QUALITY**

                                               **NOTE TO BE MAX**

       

      can you help how to do this?

        • How to split into different columns
          Grant Perkins

          Hi,

           

          I would like to bring the below as mentioend below:

           

          inv# track# comp name# balance# comments1 comemnts2 comments3 comments4

          7681774 ab4578 ab1254g,lt compy 197894 balance not paid issue in qualitynote

          Inv No Track# COMP NAME

          7681774 ab4578 AB1254G,LT COMPANY

          Balance: 197894

          (02/02/2011) ***BALANCE NOT PAID ***

          **ISSUE IN QUALITY**

          **NOTE TO BE MAX**

           

          can you help how to do this?[/QUOTE]

           

          Does the input file really look like that?

           

          What happens to it if you post it using the [URL="http://www.monarchforums.com/showthread.php?2290-Quick-FYIs-New-Message-Formatting-Options-How-to-Post-Sample-Reports-Properly"]CODE[/URL] tags that are available to help retain formatted information?

           

           

          Grant

            • How to split into different columns
              rdbabu _

               

               

                   7681774   AB4578                        AB1245G,LT COMPPY

                                                 Balance: 197894

                        (02/02/2011)             **BALANCE NOT PAID**

                                                 **ISSUE IN QUALITY**

                                                 **NOTE TO BE MAX**

               

              /CODE

                • How to split into different columns
                  Grant Perkins

                  [CODE]

                   

                   

                  7681774 AB4578 AB1245G,LT COMPPY

                  Balance: 197894

                  (02/02/2011) **BALANCE NOT PAID**

                  **ISSUE IN QUALITY**

                  **NOTE TO BE MAX**

                   

                  /CODE[/QUOTE]

                   

                  OK.

                   

                  So the output you need must look like this?:

                   

                  7681774 AB4578 "AB1245G,LT COMPPY 197894" BALANCE NOT PAID ISSUE IN QUALITY NOTE TO BE MAX

                   

                  (Ignore the " " marks - I included them only to identify that a single field is to be extracted.)

                   

                   

                  The Date information is not required?

                   

                  I will guess that there can be a variable number of Comment lines. Is there always at least one comment line?

                   

                  If there are records with no comment lines do they have a blank line below the "Balance:" line?

                   

                  Grant

                    • How to split into different columns
                      rdbabu _

                      Grant - Please find my answers to your questions.

                       

                      The Date information is not required?

                      Answer: Yes - Required

                       

                      I will guess that there can be a variable number of Comment lines. Is there always at least one comment line?

                      Answer: Yes I will always get at least one comment line and also few time I may get more than 6 comment lines

                       

                      If there are records with no comment lines do they have a blank line below the "Balance:" line?

                      Answer: There will not be such scenario at all

                       

                      Please help me

                       

                      Thanks.. Bob

                        • How to split into different columns
                          Grant Perkins

                          Hi Bob,

                           

                          OK, this looks like it needs a 3 line SAMPLE trapped on the first line to give you a DETAIL template. Based on the single sample record the trap required looks quite normal. Are you able to tap all the records successfully or are there some complications with that?

                           

                          The fields from that line will give you Inv#, Track# and Company Name.

                           

                          In the second line of the sample it looks like you shoudl be able to define the field for the Balance.

                           

                          On the third line you will have the field for the Date and then another field for a Comment. What you need to do is make the Comment field a Multi-Line field so that is will collect all the comment rows in a single field during the extraction process. This will work foro a large number of rows so if you have no more than 6 rows youshould not have any problems.

                           

                          In the template create a field on row 3 for the comments and make if wide enough to take any possible comment size. Right click on the field to access the Advanced properties and set and "End On" selection that will successfully stop the field where you want it to stop. If you have not used that option before I would strongly suggest experiementing with the different settings for a a few minutes. It's a very powerful tool and is much easier to understand and see how it can be useful if you experiment. Also more memorable for the future!

                           

                          Once you have that set up your table should gice all that you want unless you need to report or output the (multiple) comments in separate fields. If you do need to separate them yoou will need to create some calculated fields  - comment1, comment2, etc.,  probably 6 as a minimum based on your information provided - and use the TEXTLINE function in the calculated field formula to pick the line for each field.

                           

                          Does all of that make sense?

                           

                          If you try it and find other problems let us know and we can look at working out what needs to change to make it work as you need it to work.