3 Replies Latest reply: May 15, 2014 10:03 AM by cferguso _ RSS

    Join fields together from multiple lines

    cferguso _



      I am new to Monarch. I am using Monarch Pro v10. I have data that is on multiple lines that I need to concatenate.


      The data is similar to the following:


      Account #     Line #               Comments

      1                   1                    aaaaaaaa

      1                   2                    bbbbbbbb

      1                   3                    cccccccc

      2                   1                    dddddddd

      2                   2                    eeeeeeee



      I need to concatenate the Comments for each Account #.


      Any suggestions?



        • Join fields together from multiple lines
          Data Kruncher

          Hello and welcome to the forum!


          Nothing like diving into the deep end for your first challenge!


          As you've likely discovered, Monarch doesn't share data between records (or rows) like a spreadsheet does. So we need to devise an alternate solution here.


          I've devised, and a have tested, a two-part solution for you. There may be another way, but it isn't jumping out at me just now.


          First up, capture your Account number and the Comments fields for each line. This will give you five rows in your table with the sample you've provided.


          Next, create a new summary. Use the Account as your key field. Double click the field name and on the Display tab, check the option to suppress duplicate values, and set the "insert blank lines after each key value change" to 1. OK all that.


          Set the Comments field as an Item, and add the count as the only measure field.


          This should give you something like this:


          Account Comments count[/FONT]

          1       aaaaaaaa 1[/FONT]

                  bbbbbbbb 1[/FONT]

                  cccccccc 1[/FONT]


          2       dddddddd 1[/FONT]

                  eeeeeeee 1[/FONT]



          Now export the summary to a "Fixed-length Text" file.


          This concludes the first part of the process.


          To start the next part, open the new text file in a new Monarch session. Capture a one line detail template using the numeric trap for your account number field, and paint the account number and comments field. Open the properties for the Comments field, and on the Advanced tab, select the "End of Left Justification" option in the "End Field On" section. This is where the extra blank line that we added in the summary after each account number comes in handy.


          That'll do it. In your table, you should now have:


          Account Comments[/FONT]

              1 aaaaaaaa bbbbbbbb cccccccc[/FONT]

              2 dddddddd eeeeeeee[/FONT]



          If this is going to be an on-going process for you, you can combine all of this into a couple of model and project files, and glue it all together with a little batch file.


          Did I mention that you've started in the deep end somewhat?




            • Join fields together from multiple lines
              Grant Perkins

              As Kruncher says  - a great early project to pick up when yuoo are just getting into Monarch! Seems to happen so often to people it's almost normal. 


              One option that occurs to me is that IF the data is really presented as per your sample (or close to that) you might be able to trap you detail records based on the Line Number. If line one of a group of lines for an account is always 1 (or there is something equally unique about something in the report that would help to identify the 'location' of the first line for an account) then you could trap using that number, extract the Account number form the line and then use a multiplione field definition, as Kruncher describes, to extract all the text lines.


              Note that you would need to specify the number as "1" (for example) not just as " a number". There may be another character string in the report that you could use for the same approach if the number is variable.


              You would also need to be certain that such a record selection rule will always apply for that report/model combination. If consistency is doubtful then  Kruncher's solution would be my recommendation too.