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

    Multiple Detail Types Single Model

    bbeadle _

      I have a report I need to develop a model for.  Here's a representation of how the data looks.

      _____________________________________________

      03/24/03 AAA 33-2321-33  $2.33

                   Acme Products

                   One box of widgets.

      03/25/03 BBB 92-2424-29  $1.23

                   Airline tickets.

      03/26/03 AAA 22-2222-22 $20.22

                   Al's Restaurant

                   Two large pizzas

      03/27/03 BBB 99-3333-33 $33.33

                   A blue truck with whitewall

                   tires and a trailer hitch

                   and a loud horn.

      _________________________________________

      As you can see, "BBB" type lines can have multiple lines of narrative.  So can "AAA" lines.

       

      However, "AAA" lines always have their first line of narrative as the vendor name.

       

      So I created a template that takes the first line below the detail and assumes that's a vendor name.  Then it takes the 3rd line and until the next detail line to be the description.

       

      Then I added a computed field that checks the "AAA" or "BBB" code.  If it's an "AAA" type then the first line goes into a computed field called "Vendor," and the other lines become "DESC".  If it's a "BB" type, then it all gets concatenated into "DESC" and "Vendor" is left blank.

       

      Where I'm getting into trouble is that sometimes on "AAA" types there's no description at all.  Just a vendor name.  And sometimes on the "BBB" types there's only one line of description.  In both cases my working field that starts on the 3rd row pulls from the description of the record following.  Thus I get garbled results on detail records that have less than two lines to work with.

       

      Is there some way to tell a model that a given field may not always appear?

        • Multiple Detail Types Single Model
          Grant Perkins

          Hi,

           

          I like the nice touch on the data. Including the 'loud horn' suggests some creativity lurking there - which may come in handy for this little problem!

           

          I reckon your way forward is along the lines of setting up an APPEND using the DETAIL LINE for trapping pretty much the same trap (it sounds odd but can work) but using 2 line template. On the second row paint the text field to the max width you are likely to find.

           

          Set the Field OPTIONS (ADVANCED TAB in V7) to 'End Field on' to 'Non-Blank Preceeding string' of a suitable number of characters. 2 will work for the sample.

           

          As long as you are not likely to exceed 256 chars a character  field will be OK. Otherwise make it a Memo field.

           

          That will get all the text out. For the AAA fields you will need to slice and dice the text to separate the VENDOR NAME from the rest.

           

          Now that might well get a little trickier and some of the methods may be a little different depending on which version of Monarch you have available. Mostly the problem is likely to be that the multi line field will, in effect, concatenate the various rows so you may need to cheat to include a 'marker' for the start/end of the rows.

           

          That may start to get slightly complex so it would be good to know which version you have and whether Standard or Pro and how much you have used functions like INSTR, SUBSTR, IF and so on. Also some of the new functions in V7 if you have V7. (I need a guide as to whether the suggestions are enough or if you would prefer a detailed description and, of so, how detailed should I try to make it! I suspect that the suggestion alone may be enough for you to make progress but I can't be sure.)

           

          Assuming this makes sense so far you can probably work out the rest but if you get stuck slicing and dicing the text I have a way forward for that as well, based on the data format that you posted, which I can document taking into account which version you have.

           

          I hope this helps.

           

          Regards,

           

          Grant

           

            Originally posted by bbeadle:

          I have a report I need to develop a model for.  Here's a representation of how the data looks.

          _____________________________________________

          03/24/03 AAA 33-2321-33  $2.33

                       Acme Products

                       One box of widgets.

          03/25/03 BBB 92-2424-29  $1.23

                       Airline tickets.

          03/26/03 AAA 22-2222-22 $20.22

                       Al's Restaurant

                       Two large pizzas

          03/27/03 BBB 99-3333-33 $33.33

                       A blue truck with whitewall

                       tires and a trailer hitch

                       and a loud horn.

          _________________________________________

          As you can see, "BBB" type lines can have multiple lines of narrative.  So can "AAA" lines.

           

          However, "AAA" lines always have their first line of narrative as the vendor name.

           

          So I created a template that takes the first line below the detail and assumes that's a vendor name.  Then it takes the 3rd line and until the next detail line to be the description.

           

          Then I added a computed field that checks the "AAA" or "BBB" code.  If it's an "AAA" type then the first line goes into a computed field called "Vendor," and the other lines become "DESC".  If it's a "BB" type, then it all gets concatenated into "DESC" and "Vendor" is left blank.

           

          Where I'm getting into trouble is that sometimes on "AAA" types there's no description at all.  Just a vendor name.  And sometimes on the "BBB" types there's only one line of description.  In both cases my working field that starts on the 3rd row pulls from the description of the record following.  Thus I get garbled results on detail records that have less than two lines to work with.

           

          Is there some way to tell a model that a given field may not always appear? /b[/quote]

           

          [size="1"][ January 09, 2004, 07:57 PM: Message edited by: Grant Perkins ][/size]

          • Multiple Detail Types Single Model
            bbeadle _

            Ok, what you're saying makes sense.  I had thought about initially putting it all into one field then parsing it out myself.  The problem is, as you point out, that it concatenates all of it together so I don't have any delimiter to work with to re-separate the vendor name.

             

            I'm working with Monarch Pro v7.0.  Can you give me some advice on how I can parse the vendor name back out?

             

            Thanks,

            -Bert

             

            Originally posted by Grant Perkins:

            Hi,

             

            I like the nice touch on the data. Including the 'loud horn' suggests some creativity lurking there - which may come in handy for this little problem!

             

            I reckon your way forward is along the lines of setting up an APPEND using the DETAIL LINE for trapping pretty much the same trap (it sounds odd but can work) but using 2 line template. On the second row paint the text field to the max width you are likely to find.

             

            Set the Field OPTIONS (ADVANCED TAB in V7) to 'End Field on' to 'Non-Blank Preceeding string' of a suitable number of characters. 2 will work for the sample.

             

            As long as you are not likely to exceed 256 chars a character  field will be OK. Otherwise make it a Memo field.

             

            That will get all the text out. For the AAA fields you will need to slice and dice the text to separate the VENDOR NAME from the rest.

             

            Now that might well get a little trickier and some of the methods may be a little different depending on which version of Monarch you have available. Mostly the problem is likely to be that the multi line field will, in effect, concatenate the various rows so you may need to cheat to include a 'marker' for the start/end of the rows.

             

            That may start to get slightly complex so it would be good to know which version you have and whether Standard or Pro and how much you have used functions like INSTR, SUBSTR, IF and so on. Also some of the new functions in V7 if you have V7. (I need a guide as to whether the suggestions are enough or if you would prefer a detailed description and, of so, how detailed should I try to make it! I suspect that the suggestion alone may be enough for you to make progress but I can't be sure.)

             

            Assuming this makes sense so far you can probably work out the rest but if you get stuck slicing and dicing the text I have a way forward for that as well, based on the data format that you posted, which I can document taking into account which version you have.

             

            I hope this helps.

             

            Regards,

             

            Grant

            • Multiple Detail Types Single Model
              Grant Perkins

              Hi Bert,

               

              Based on th sample (I hoped you might spot something else on the full report) I added some spaces -- as few as necessary ideally -- to the BEGINNING of the text field. i.e. start it left of where the text aligns.

               

              Leading spaces are not auto trimmed. That means that, absent any other way of getting the LEN() of the real first line (another append for just that line? But conditional processing probably required). you can LTRIM the field and then INSTR on the string of spaces to get an end point for line one text. Then SUBSTR or EXTRACT or whatever.

               

              The rest of your text will look a bit messy, depending on how many spaces you had to add to allow for any vagaries in the existing spaces in the text. If it matters you can tidy up using STUFF (In V7) to STUFF one space where there were "X" spaces before. As far as I can see you would need to do this iteratively for as many space blocks as you might have. Not too bad it you only have 2 or 3 lines. Not so good if you have 100!

               

              Now 'SUBSTITUTE' for all occurrences in a field would be nice ... or even an INFIELD TRIM.

               

              If not STUFF then repeat the idea for the first field and simply re-concatenate the results each time and then process the new field and so on.

               

              I'm sure I have seen a more elegant solution to this part but it doesn't come to mind at the moment.

               

              I suspect you won't need them but, if you get stuck, I have some formulae that form a basis for the task.

               

              Does that make sense at all?

               

              Grant

               

               

              Originally posted by bbeadle:

              Ok, what you're saying makes sense.  I had thought about initially putting it all into one field then parsing it out myself.  The problem is, as you point out, that it concatenates all of it together so I don't have any delimiter to work with to re-separate the vendor name.

               

              I'm working with Monarch Pro v7.0.  Can you give me some advice on how I can parse the vendor name back out?

               

              Thanks,

              -Bert

               

              /b[/quote]

              • Multiple Detail Types Single Model
                Steve Caiels

                Hi,

                 

                Try the following.  It works on the sample you gave.

                 

                1.     Create a two line detail template using the date line and 1st vendor/description line.  Trap on the slash of the date or something similar.  Auto define the fields so that all the vendor/description goes into one field.  Name the AAA/BBB field TYPE and the vendor/description field ALL.

                2.     Create an append template using the same two lines and trap, this time define a single field from the 1st line of the vendor/description and call it TOP

                3.     In the table create two calculated fields:

                For Vendor use  If(TYPE=”aaa”,top,””)

                For  Description use if(type="AAA",trim(extract(all,top)),all)

                 

                So if the type is AAA, the ALL field must contain a vendor. So the expression will extract from the end of the vendor name (that will be in the TOP field) to the end of the ALL field.  It will all make sense when you see it in the table.

                 

                If my description is not clear, I can email you the report and model if you like.  Just drop me a private message.

                 

                Cheers

                Steve

                 

                [size="1"][ January 12, 2004, 02:55 PM: Message edited by: Steve Caiels ][/size]

                • Multiple Detail Types Single Model
                  Grant Perkins

                  That's the one Steve.

                   

                  I knew it was there somewhere - just got too involved with a couple of other things to get back to the basics on Bert's problem.

                   

                  Nice solution. Excellent use of EXTRACT. But I would still like SUBSTITUTE as previously mentioned ...      

                   

                  Grant

                   

                  EDIT:

                   

                  Just spotted another option. You can omit the

                   

                  "For Vendor use If(TYPE=”aaa”,top,””)"

                   

                  calculation field by selecting VENDOR directly according to the code AAA as a preceeding string on the PREVIOUS LINE.

                   

                  Use Field>Properties>Advanced>Start field on>"String anywhere in the previous line".

                   

                  Then simply extract from as Steve proposes.

                   

                  However that will ONLY WORK WHERE A SINGLE CODE or a shared COMPONENT STRING in the code IS THE IDENTIFIER and that identifier is never likely to appear as a string anywhere else in the row. If the VENDOR field might be required for all of a number of codes then Steve's option is the way to go and use the calculated field and the IF function to obtain the desired result.

                   

                  [size="1"][ January 12, 2004, 09:32 PM: Message edited by: Grant Perkins ][/size]