4 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    Model Building

    NECharter _

      I'm stuck with this model. I need to pay people commissions off of this report, and I can't get a model built to pull what I need.  Here is a sample of the report:

       

      NP  55555555805116048                  JOB NUMBER  : 423227       WO RSNS: NP            ENTERED ON - BY:  10/21/02 05:45 - ZZQ NPAY

          XXXXX,MELLISSA             UNKNOWN TECH - TYPE : 1173 -       BATCH #: 00000         CHANGED ON - BY:  11/12/02 17:45 - DAW FNBL

          822 4TH AVE                        INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555010472138  SALESREP: 00051

          ALTOONA PA 16602-3708              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-4379      CAMPAIGN:

                                             CATG - UNITS: 06 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

          CALLER: AUTOMATICALLY GENERATED    BILL TYPE   : 01           BILL DT: 10/21/02      VIP FLAG:                   SUB MISC:

          CMPL CD:

          BILLSERVBEFORE QTYAFTER QTYDISC: B3/B3/0001/0001/     PV/AA/0001/0001/     PV/AB/0001/0001/     PV/CA/0001/0001/

                                                   PV/EA/0001/0001/     PV/EB/0001/0001/     PV/EC/0001/0001/     PV/HB/0001/0001/

                                                   PV/SH/0001/0001/     PV/MX/0001/0001/     PV/TM/0001/0001/     PV/SE/0001/0001/

       

      DV  55555555505021007                  JOB NUMBER  : 443335       WO RSNS: DY            ENTERED ON - BY:  11/05/02 10:33 - NWF FNBN

          XXXXX,RICHARD              UNKNOWN TECH - TYPE : 1150 -       BATCH #: 00000         CHANGED ON - BY:  11/14/02 09:39 - GDC FNBA

          410 12TH ST                        INSTALL DATE: 11/13/02     PREPAY :     0.00      SUB #   : 5555555510517882  SALESREP: 00051

          ALTOONA PA 16602-3625              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-7568      CAMPAIGN:

                                             CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

          CALLER:                            BILL TYPE   : 01           BILL DT: 11/09/02      VIP FLAG:                   SUB MISC:

          CMPL CD:

          BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0001/0001/     AB/AB/0001/0001/

       

      DV  55555525405050010                  JOB NUMBER  : 432250       WO RSNS: DS            ENTERED ON - BY:  10/28/02 09:25 - PAJ FNB9

          IUZZOLINO,MIKE A           MR      TECH - TYPE : 1171 -       BATCH #: 00000         CHANGED ON - BY:  11/13/02 10:27 - PAJ FNB8

          137 STRATFORD CT                   INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555540109429  SALESREP: 00051

          HOLLIDAYSBURG PA 16648-9254        INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-2171      CAMPAIGN:

                                             CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-555-1576      SUB DISC:

          CALLER: DANA                       BILL TYPE   : 01           BILL DT: 10/29/02      VIP FLAG:                   SUB MISC:

          CMPL CD:

          BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0003/0003/     T1/T1/0001/0001/

       

      The more service codes that were added or removed, the more lines that show up on the report.  I have found that some work orders can have as many as 9 lines of info on the service call, but whether 1 line, or 9 lines, the next sale is only 1 blank line away.  How can I build a model to grab all of the information for a sale when the length of that record has variable lengths up and down the report?

       

      I know the pasted reported did not come through very well in the post, so let me say that there are always at most 4 service codes per line, and they are lined up nicely going down the report.

       

      Any help is appreciated.

        • Model Building
          Grant Perkins

          Hi,

           

          Just to clarify that the report has a consistent format (though maybe not exactly what appears in the post judging from your comments) from the first line of each record (e.g. NP  55555555805116048      etc.) to the row starting "BILLSERVBEFORE QTY--AFTER ". But after that there can be a different number of lines (1 to 9 or perhaps more?) before a blank line and then the start of the next record. IS that right so far? Is there ever a situation where there are NO service codes? (assuming that the last lines of the record block are the service codes).

           

          As long as the report ALWAYS has at least one service code line then one possibility is to use the Service Code line as the Detail line for the template and the rest of the record above it as an append, selecting which ever bits of data you require.

           

          If you have no use for the individual lines of detail giving the service codes there are alternative approaches using, for example, a multiline field which might start at the "BILLSERVBEFORE QTYAFTER QTYDISC"  line and extend until the blank line is encountered or the next template is encountered. The Service Codes could then be treated as a memo field.

           

          There may be other possibilities that would be better for your purpose but I'm working a bit blind here not knowing how good a guide to the layout the posted report offers, whether the service codes are significant and part of your calculations depend upon them, which data fields are to be extracted and also which version of Monarch you have access to.

           

          If the ideas above don't move you forward with your solution let me know (via post or private message) where the problems lie and I will gladly reassess the suggestions!

           

          Best Regards,

           

          Grant

           

          Originally posted by NECharter:

          I'm stuck with this model. I need to pay people commissions off of this report, and I can't get a model built to pull what I need.  Here is a sample of the report:

           

          NP  55555555805116048                  JOB NUMBER  : 423227       WO RSNS: NP            ENTERED ON - BY:  10/21/02 05:45 - ZZQ NPAY

              XXXXX,MELLISSA             UNKNOWN TECH - TYPE : 1173 -       BATCH #: 00000         CHANGED ON - BY:  11/12/02 17:45 - DAW FNBL

              822 4TH AVE                        INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555010472138  SALESREP: 00051

              ALTOONA PA 16602-3708              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-4379      CAMPAIGN:

                                                 CATG - UNITS: 06 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

              CALLER: AUTOMATICALLY GENERATED    BILL TYPE   : 01           BILL DT: 10/21/02      VIP FLAG:                   SUB MISC:

              CMPL CD:

              BILLSERVBEFORE QTYAFTER QTYDISC: B3/B3/0001/0001/     PV/AA/0001/0001/     PV/AB/0001/0001/     PV/CA/0001/0001/

                                                       PV/EA/0001/0001/     PV/EB/0001/0001/     PV/EC/0001/0001/     PV/HB/0001/0001/

                                                       PV/SH/0001/0001/     PV/MX/0001/0001/     PV/TM/0001/0001/     PV/SE/0001/0001/

           

          DV  55555555505021007                  JOB NUMBER  : 443335       WO RSNS: DY            ENTERED ON - BY:  11/05/02 10:33 - NWF FNBN

              XXXXX,RICHARD              UNKNOWN TECH - TYPE : 1150 -       BATCH #: 00000         CHANGED ON - BY:  11/14/02 09:39 - GDC FNBA

              410 12TH ST                        INSTALL DATE: 11/13/02     PREPAY :     0.00      SUB #   : 5555555510517882  SALESREP: 00051

              ALTOONA PA 16602-3625              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-7568      CAMPAIGN:

                                                 CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

              CALLER:                            BILL TYPE   : 01           BILL DT: 11/09/02      VIP FLAG:                   SUB MISC:

              CMPL CD:

              BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0001/0001/     AB/AB/0001/0001/

           

          DV  55555525405050010                  JOB NUMBER  : 432250       WO RSNS: DS            ENTERED ON - BY:  10/28/02 09:25 - PAJ FNB9

              IUZZOLINO,MIKE A           MR      TECH - TYPE : 1171 -       BATCH #: 00000         CHANGED ON - BY:  11/13/02 10:27 - PAJ FNB8

              137 STRATFORD CT                   INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555540109429  SALESREP: 00051

              HOLLIDAYSBURG PA 16648-9254        INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-2171      CAMPAIGN:

                                                 CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-555-1576      SUB DISC:

              CALLER: DANA                       BILL TYPE   : 01           BILL DT: 10/29/02      VIP FLAG:                   SUB MISC:

              CMPL CD:

              BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0003/0003/     T1/T1/0001/0001/

           

          The more service codes that were added or removed, the more lines that show up on the report.  I have found that some work orders can have as many as 9 lines of info on the service call, but whether 1 line, or 9 lines, the next sale is only 1 blank line away.  How can I build a model to grab all of the information for a sale when the length of that record has variable lengths up and down the report?

           

          I know the pasted reported did not come through very well in the post, so let me say that there are always at most 4 service codes per line, and they are lined up nicely going down the report.

           

          Any help is appreciated.[/b][/quote]

          • Model Building
            NECharter _

            Thanks for the quick response.. Yes the Service codes are a very imporatnt part of the model... I need them to determine the commission to pay.  There will always be at least 1 service code showing, and yes the service codes are the fields after the "BILLSERVBEFORE QTY--AFTER".

             

            The report is in a consistant format, and I do need a few of the fields from the data above, like the date, and the salesrep number.

             

            Originally posted by Grant Perkins:

            Hi,

             

            Just to clarify that the report has a consistent format (though maybe not exactly what appears in the post judging from your comments) from the first line of each record (e.g. NP  55555555805116048      etc.) to the row starting "BILLSERVBEFORE QTY--AFTER ". But after that there can be a different number of lines (1 to 9 or perhaps more?) before a blank line and then the start of the next record. IS that right so far? Is there ever a situation where there are NO service codes? (assuming that the last lines of the record block are the service codes).

             

            As long as the report ALWAYS has at least one service code line then one possibility is to use the Service Code line as the Detail line for the template and the rest of the record above it as an append, selecting which ever bits of data you require.

             

            If you have no use for the individual lines of detail giving the service codes there are alternative approaches using, for example, a multiline field which might start at the "BILLSERVBEFORE QTYAFTER QTYDISC"  line and extend until the blank line is encountered or the next template is encountered. The Service Codes could then be treated as a memo field.

             

            There may be other possibilities that would be better for your purpose but I'm working a bit blind here not knowing how good a guide to the layout the posted report offers, whether the service codes are significant and part of your calculations depend upon them, which data fields are to be extracted and also which version of Monarch you have access to.

             

            If the ideas above don't move you forward with your solution let me know (via post or private message) where the problems lie and I will gladly reassess the suggestions!

             

            Best Regards,

             

            Grant

             

              /size[quote]quote:[/size]Originally posted by NECharter:

            I'm stuck with this model. I need to pay people commissions off of this report, and I can't get a model built to pull what I need.  Here is a sample of the report:

             

            NP  55555555805116048                  JOB NUMBER  : 423227       WO RSNS: NP            ENTERED ON - BY:  10/21/02 05:45 - ZZQ NPAY

                XXXXX,MELLISSA             UNKNOWN TECH - TYPE : 1173 -       BATCH #: 00000         CHANGED ON - BY:  11/12/02 17:45 - DAW FNBL

                822 4TH AVE                        INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555010472138  SALESREP: 00051

                ALTOONA PA 16602-3708              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-4379      CAMPAIGN:

                                                   CATG - UNITS: 06 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

                CALLER: AUTOMATICALLY GENERATED    BILL TYPE   : 01           BILL DT: 10/21/02      VIP FLAG:                   SUB MISC:

                CMPL CD:

                BILLSERVBEFORE QTYAFTER QTYDISC: B3/B3/0001/0001/     PV/AA/0001/0001/     PV/AB/0001/0001/     PV/CA/0001/0001/

                                                         PV/EA/0001/0001/     PV/EB/0001/0001/     PV/EC/0001/0001/     PV/HB/0001/0001/

                                                         PV/SH/0001/0001/     PV/MX/0001/0001/     PV/TM/0001/0001/     PV/SE/0001/0001/

             

            DV  55555555505021007                  JOB NUMBER  : 443335       WO RSNS: DY            ENTERED ON - BY:  11/05/02 10:33 - NWF FNBN

                XXXXX,RICHARD              UNKNOWN TECH - TYPE : 1150 -       BATCH #: 00000         CHANGED ON - BY:  11/14/02 09:39 - GDC FNBA

                410 12TH ST                        INSTALL DATE: 11/13/02     PREPAY :     0.00      SUB #   : 5555555510517882  SALESREP: 00051

                ALTOONA PA 16602-3625              INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-7568      CAMPAIGN:

                                                   CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-000-0000      SUB DISC:

                CALLER:                            BILL TYPE   : 01           BILL DT: 11/09/02      VIP FLAG:                   SUB MISC:

                CMPL CD:

                BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0001/0001/     AB/AB/0001/0001/

             

            DV  55555525405050010                  JOB NUMBER  : 432250       WO RSNS: DS            ENTERED ON - BY:  10/28/02 09:25 - PAJ FNB9

                IUZZOLINO,MIKE A           MR      TECH - TYPE : 1171 -       BATCH #: 00000         CHANGED ON - BY:  11/13/02 10:27 - PAJ FNB8

                137 STRATFORD CT                   INSTALL DATE: 11/12/02     PREPAY :     0.00      SUB #   : 5555555540109429  SALESREP: 00051

                HOLLIDAYSBURG PA 16648-9254        INSTALL TIME: O - ALL      DEPOSIT:     0.00      H PHONE : 814-555-2171      CAMPAIGN:

                                                   CATG - UNITS: 04 - 002     DWELL  : AA            B PHONE : 814-555-1576      SUB DISC:

                CALLER: DANA                       BILL TYPE   : 01           BILL DT: 10/29/02      VIP FLAG:                   SUB MISC:

                CMPL CD:

                BILLSERVBEFORE QTYAFTER QTYDISC: AA/AA/0001/0001/     0B/0B/0003/0003/     T1/T1/0001/0001/

             

            The more service codes that were added or removed, the more lines that show up on the report.  I have found that some work orders can have as many as 9 lines of info on the service call, but whether 1 line, or 9 lines, the next sale is only 1 blank line away.  How can I build a model to grab all of the information for a sale when the length of that record has variable lengths up and down the report?

             

            I know the pasted reported did not come through very well in the post, so let me say that there are always at most 4 service codes per line, and they are lined up nicely going down the report.

             

            Any help is appreciated.[/b][/quote][/b][/size][/QUOTE]

            • Model Building
              Grant Perkins

              OK. Then I would think that trapping the Service Codes as detail lines and then appending the other fields from above would be the best route. I assume you can then calculate the commissions for each Service code (?) and so arrive at a total for the whole sale.

               

              Looks like the detail lines could be trapped using the "/" characters. Does that sound OK to you? Did you indicate that the service codes have a line each or can there be more than one code on a line?

               

              The Append section should be identifiable from the first line of the 'data' record.

               

              If I created a model based on the posted layout would that be a long way out from the real report format? I'm particulary interested in the format for the Service Code lines as they seem to be the critical and possibly most complex selections. Are the codes the complete blocks including the "/" or do you need to separate the parts of the code for the purposes of your calculations? e.g. is the single code "aa/aa/xxxx/xxxx" or do you need to see aa , aa, xxxx,  xxxx ?

               

              If you would prefer to do this without a full posting try sending me a Private Message (from your 'My Profile' section) and I will respond to that.

               

              Regards,

               

              Grant

               

              Originally posted by NECharter:

              Thanks for the quick response.. Yes the Service codes are a very imporatnt part of the model... I need them to determine the commission to pay.  There will always be at least 1 service code showing, and yes the service codes are the fields after the "BILLSERVBEFORE QTY--AFTER".

               

              The report is in a consistant format, and I do need a few of the fields from the data above, like the date, and the salesrep number.

              • Model Building
                Grant Perkins

                cgb,

                 

                Having looked at this from several angles there are many ways to extract and group the data and then manipulate what has been extracted. However the records that have multiple codes and therefore an 'array' to analyse would seem to be adding a deal of complexity to everything that would follow.

                 

                So, if it were my problem (and assuming that the original report does not contain any 'funnies' that are not obvious from the posted sample) I would pre-process the original, using MSRP, to separate each service code onto its own line.

                 

                The following command (all on one line of course!) appear to do the trick for the sample provided. It swaps the "/ " at the end of each reported code for "/<lf>".

                 

                msrp "/47/32" "/47/10" <input_file_name> <output_file_name>

                 

                The whole process should be simple after that. The service code lines become detail lines, everything else should be trappable using an append (or multiple appends using floating traps if the layout is not 100% consistent in all cases).

                 

                I hope this helps.

                 

                Grant

                 

                PS  The answers to the multiple line selection issue are several and may vary a little depending upon whether you use V5/V6 or V6.01.

                 

                If there is an interest let me know and I will post my observations.

                 

                Originally posted by Grant Perkins:

                OK. Then I would think that trapping the Service Codes as detail lines and then appending the other fields from above would be the best route. I assume you can then calculate the commissions for each Service code (?) and so arrive at a total for the whole sale.

                 

                Looks like the detail lines could be trapped using the "/" characters. Does that sound OK to you? Did you indicate that the service codes have a line each or can there be more than one code on a line?

                 

                The Append section should be identifiable from the first line of the 'data' record.

                 

                If I created a model based on the posted layout would that be a long way out from the real report format? I'm particulary interested in the format for the Service Code lines as they seem to be the critical and possibly most complex selections. Are the codes the complete blocks including the "/" or do you need to separate the parts of the code for the purposes of your calculations? e.g. is the single code "aa/aa/xxxx/xxxx" or do you need to see aa , aa, xxxx,  xxxx ?

                 

                If you would prefer to do this without a full posting try sending me a Private Message (from your 'My Profile' section) and I will respond to that.

                 

                Regards,

                 

                Grant

                 

                Originally posted by NECharter:

                Thanks for the quick response.. Yes the Service codes are a very imporatnt part of the model... I need them to determine the commission to pay.  There will always be at least 1 service code showing, and yes the service codes are the fields after the "BILLSERVBEFORE QTY--AFTER".

                 

                The report is in a consistant format, and I do need a few of the fields from the data above, like the date, and the salesrep number.[/b][/quote]