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

    Same Line Multi-Trap Issue

    mdyoung _

      [B]Monarch Version: 10.5 Pro[/B][/I]

       

      First off..... I apologize ahead of time if this seems long winded, but it's the only way I could think of to express what I'm trying to accomplish.

       

      I'm having an issue trying to trap a second append on the same line as the first append. The reason for this is because the last four columns vary in their starting position. Below is a sample of the data I have to deal with. The first half of the file has the last four columns close to the previous columns, and the second half has the columns pushed out further. However, the spacing between each of the four troublesome columns always remains the same. The only space difference is between the ORD/REF PHYS column and the BILLED column. The ORD/REF PHYS column's data will never exceed 10 characters, so technically, there's no reason why the additional spaces occur. They just do and there's no way to correct this from the primary source of the report.

       

      Sample Data:

      (NOTE: There are two rows of column headers: the first is for the append columns and the second is for the detail columns.)[/I]

       

      REPORT DATE: 02/01/11

      BILL REPORT TYPE: CA

       

      ENTRY ACCT NUMBER  ACCOUNT NAME                   BILL     SER DATE  ACCT TYPE PRI INS   POST STATUS       PERF PHYS   ORD PHYS   BILLED   LAST PAY  TOT CHGS   BALANCE

             QNTY TYPE CORP PROCEDURE          AMOUNT   #       REV SITE     COST AMT   COMMENT                             ADD EXP RCPTS

          1 11111111     TEST,CLIENT001                 1        07/21/10  ER        EXINS15    N   FB  12/05/10             ORDPHY0002 09/15/10 10/02/10                0.00

                1 ADJ  01   AA.PROCTEST01      333.50                                                                               

      1 RCP  01   EE.PROCTEST05     1339.20                                                                        

          2 22222222     TEST,CLIENT002                 1        07/21/10  OUT       EXINS07    N   FB  11/29/10 PERFPHY001  ORDPHY0012 09/18/10 10/02/10    470.79     64.48

                1 ADJ  01   AA.PROCTEST15      212.87                                                                               

      1 RCP  01   EE.PROCTEST08      193.44                                                                        

          3 33333333     TEST,CLIENT003                 1        07/22/10  ER        EXINS10    N   FB  10/16/10 PERFPHY004  ORDPHY0007 09/16/10 10/07/10   2943.00    556.83

                1 ADJ  01   AA.PROCTEST24     1107.00                                                                               

      1 RCP  01   EE.PROCTEST03      568.42                                                                        

          4 44444444     TEST,CLIENT004                 1        07/23/10  ER        EXINS05    N   FB  01/05/11             ORDPHY0009 09/17/10 10/21/10   3194.90    988.36

                1 ADJ  01   AA.PROCTEST07      528.90                                                                               

      1 RCP  01   EE.PROCTEST12     1255.53                                                                        

       

      REPORT DATE: 02/01/11

      BILL REPORT TYPE: ERA

       

      ENTRY ACCT NUMBER  ACCOUNT NAME                   BILL     SER DATE  ACCT TYPE PRI INS   POST STATUS       PERF PHYS   ORD/REF PHYS                   BILLED   LAST PAY  TOT CHGS   BALANCE

             QNTY TYPE CORP PROCEDURE          AMOUNT   #       REV SITE     COST AMT   COMMENT                             ADD EXP RCPTS

          1 55555555     TEST,CLIENT005                 1        07/20/10  ER        EXINS17    N   FB  11/30/10 PERFPHY013                                 09/04/10 09/28/10   3455.78   1557.61

                1 ADJ  01   AA.PROCTEST10      654.15                                                                               

      1 RCP  01   EE.PROCTEST23     1244.02                                                                        

          2 66666666     TEST,CLIENT006                 1        07/20/10  OUT       EXINS01    N   FB  10/01/10                                                     10/15/10                0.00

                1 ADJ  01   AA.PROCTEST07       91.32                                                                               

      1 RCP  01   EE.PROCTEST08      105.32                                                                        

          3 77777777     TEST,CLIENT007                 1        07/21/10  ER        EXINS03    N   FB  10/05/10             ORDPHY0014                     09/07/10 11/02/10   2545.80    681.43

                1 ADJ  01   AA.PROCTEST02     1422.50                                                                               

      1 RCP  01   EE.PROCTEST09      441.87                                                                        

          4 88888888     TEST,CLIENT008                 1        07/20/10  ER        EXINS03    N   FB  12/19/10             ORDPHY0007                     09/07/10 10/15/10   4531.37   2542.71

                1 ADJ  01   AA.PROCTEST17      656.45                                                                               

      1 RCP  01   EE.PROCTEST02     1332.21                                                                        

       

      /CODE

       

       

       

       

       

       

      Here's how I currently have my templates:

      Detail

        TRAP LINE: ßßßßßßß   Ñ                                 .ÑÑ

      SAMPLE LINE:           1 ADJ  01   AA.PROCTEST01      333.50

      /CODE

      /LIST

      Append 1

        TRAP LINE: ßß  Ñß                                                       /  /

      SAMPLE LINE:     1 11111111     TEST,CLIENT001                 1        07/21/10  ER        EXINS15    N   FB  12/05/10             ORDPHY0002 09/15/10 10/02/10   2522.09    849.39

      /CODE

      /LIST

      Header

        TRAP LINE: report date

      SAMPLE LINE: REPORT DATE: 02/01/11

      SAMPLE LINE: BILL REPORT TYPE: CA

      /CODE

      /LISTHere's the trapping I originally did for the 2nd Append to catch the last four floating columns with the "Floating Trap" option checked:

        TRAP LINE:                                                                                ÑÑ/ÑÑ/ÑÑßÑÑ/ÑÑ/ÑÑ

      SAMPLE LINE:     1 11111111     TEST,CLIENT001                 1        07/21/10  ER        EXINS15    N   FB  12/05/10             ORDPHY0002 09/15/10 10/02/10   2522.09    849.39

      /CODE

       

      This worked........ HOWEVER....... I noticed that every now and then, there would be an instance where there was no BILLED date. When this happens, only the LAST PAY would have a date and the BALANCE value would always be 0.00. This is where I'm running into my problem.

       

      I tried modifying my trap on the 2nd append to the following by removing the trap over the BILLED column and adding a period over the BALANCE column, but it didn't like it.

        TRAP LINE:                                                                                ßÑÑ/ÑÑ/ÑÑ                 .

      SAMPLE LINE:     2 66666666     TEST,CLIENT006                 1        07/20/10  OUT       EXINS01    N   FB  10/01/10                                 10/15/10                0.00

      /CODE

       

      This is where I REALLY need the help of all you gurus out there. No matter what I try, I can't seems to capture those last four floating columns.

       

      Also, this needs to be a one-pass solution because the model will be used as the reporting model in our Monarch BI (aka Datawatch|ES) system.

       

      Any help is greatly appreciated as I am stuck at this point.

       

      Thanks,

       

      Micheal

        • Same Line Multi-Trap Issue
          Olly Bond

          Hello Michael,

           

          I think this is do-able. You shouldn't need two appends - just one, that grabs a large chunk of data and then use calculated fields in the table to sort it out. The intermittent data is tricky, but you should be able to manage this with Intrim(), Lsplit() and some tests on the number of "." and " " in each line to determine whether you have all the columns or not. You can get a count of a character (e.g. "x") in a field with len(intrim())-len(strip(intrim(;"x") - using this with " " and "." for "x" will help you test whether the line has all the columns populated or not.

           

          If the intermittent data is only varying in certain ways then you'll be OK - but if sometimes you miss the Billed date and sometimes the Last Pay date, or you sometimes miss the TotChgs and sometimes the Balance, then we'll need to revisit the two appends. You might be OK even then using the Cleared By feature in 10.5 on the templates.

           

          Hope this helps,

           

          Olly

            • Same Line Multi-Trap Issue
              mdyoung _

              Thanks for the quick reply, Olly. You've sparked a good idea in my head to try. I'll post again later with the solution that worked for me.

               

              Thanks,

               

              Micheal

                • Same Line Multi-Trap Issue
                  mdyoung _

                  Here's what I wound up doing. I got rid of the 2nd append and modified the first append by capturing all four columns in one field (named: EndingData_RAW). I made the field wide enough (70 chrs) to capature all data no matter how close or far the columns appeared on the report. Since the actual space between the four columns never changes, I was able to create four calculated fields.

                   

                   

                   

                   

                  Here are the calculated fields' setup data:

                  BillDate (Date/Time - Short Date)

                  [SIZE=2]Ctod(Left(Trim(), 8), "m/d/y", "d")[/SIZE]

                  /CODE

                  LastPayDate (Date/Time - Short Date)

                  [SIZE=2]Ctod(SubStr(Trim(), 10, 8), "m/d/y", "d")[/SIZE]

                  /CODE

                  TotalChgs (Numeric - 2 decimal places)

                  [SIZE=2]Val(SubStr(Trim(), 18, 10))[/SIZE]

                  /CODE

                  Balance (Numeric - 2 decimal places)

                  [SIZE=2]Val(SubStr(Trim(), 28, 10))[/SIZE]

                  /CODE

                  /LISTIt works for me. Thanks again, Olly, for jolting my brain in the right direction. Guess my gears got stuck. That's what aging will do for you.

                   

                  Thanks,

                   

                  Micheal