4 Replies Latest reply: May 15, 2014 10:06 AM by Olly Bond RSS

    multiple detail in one line

    emanc2k _

      Hi guys, I'm running into some trouble monarching the following.  Any pointers?

       

      Parent_Acctno    Words with multiple child_acctno embedded

      -


      12345678           Apple #512345   Orange 234567, 45678

      12345679           567783 KIWI   MANGO

      23456790           TOMATO     #234566 & 234958

       

      etc.

       

      I want the report to have child records (detail) w/ appending parent_acctno

      i.e.

      12345678           512345

      12345678           234567

      12345678           45678

       

      etc.

       

       

      I tried using floating traps on the child and selecting the child and "remaining text" as the selected fields and creating a new report with the parent, "remaining fields" and repeating the process.  Is there a better way?

       

      Thanks

        • multiple detail in one line
          Olly Bond

          Hello emanc2k,

           

          If you know that there are never more than 40 child account numbers, then you can do this in one model using MCR. If there are exceptional rows with more than 40 child account numbers, but fewer than 250, then it can be done in two models with a lot of repetitive calculated fields.

           

          If there isn't a safe known upper limit, then you'll need to use Monarch Utility to prep the data. I think you should be able to stick with one quite simple model after this - it depends on the regularity of the characters in between the child entries - is there sometimes a # and sometimes a comma and sometimes a space?

           

          HTH,

           

          Olly

            • multiple detail in one line
              emanc2k _

              The problem is the child account fields don't fall into columns.  Think of it as:

              parent account | Long string of text with multiple embedded child account #'s.

               

              Btw, there are less than 10 child accounts per parent.

                • multiple detail in one line
                  Joe Berry

                  Is the child account always the same length?  From your sample, it appears it can be 5 or 6 digits. 

                   

                  This can be done another way with one model, but it may need to be prepped before we start trapping.  As Olly indicated "it depends on the regularity of the characters in between the child entries - is there sometimes a # and sometimes a comma and sometimes a space?"

                    • multiple detail in one line
                      Olly Bond

                      Hello emanc2k,

                       

                      It sounds like a simple one pass model - the MCR trick will solve this easily enough as long as the dividing characters between the child accounts are regular, or if not, we could probably handle them with a calculated field without needing to use Prep.

                       

                      If you can, please email me a report at olly@greenbar.info[/email] and I'll send you a model file back.

                       

                      Best wishes,

                       

                      Olly