4 Replies Latest reply: May 15, 2014 9:54 AM by jw2523 _ RSS

    Null Field Filled with Previous Row's Value

    jw2523 _

      Hi,

       

      I'm using Monarch Pro v. 7 and currently having a data extraction issue. Below is my data file:

       

      [font="courier"]  DATE       PATIENT            AT  NO

      -


      -


      -- -


       

      LOCATION 01

      INSURANCE PLAN/GRP (MCD)

                  TOTAL FOR PLAN/GRP      341

       

      INSURANCE PLAN/GRP (MCDMC)

                  TOTAL FOR PLAN/GRP      395

       

      INSURANCE PLAN/GRP (MMCFS)

                  TOTAL FOR PLAN/GRP        1

       

      INSURANCE PLAN/GRP (NYPFC)

                  TOTAL FOR PLAN/GRP       91

       

      INSURANCE PLAN/GRP (OTH)

                  TOTAL FOR PLAN/GRP       19

       

                  TOTAL FOR LOCATION      847

       

       

      LOCATION 02

      INSURANCE PLAN/GRP (CS)

                  TOTAL FOR PLAN/GRP        9

       

      INSURANCE PLAN/GRP (MCD)

                  TOTAL FOR PLAN/GRP       55

       

      INSURANCE PLAN/GRP (MCDMC)

                  TOTAL FOR PLAN/GRP       72

       

      INSURANCE PLAN/GRP (MMCFS)

                  TOTAL FOR PLAN/GRP        2

       

      INSURANCE PLAN/GRP (NYPFC)

                  TOTAL FOR PLAN/GRP       15

       

      INSURANCE PLAN/GRP (OTH)

                  TOTAL FOR PLAN/GRP        5

       

      INSURANCE PLAN/GRP (RES)

                  TOTAL FOR PLAN/GRP       11

       

                  TOTAL FOR LOCATION      160

       

       

      LOCATION 03

      INSURANCE PLAN/GRP (CPP)

                  TOTAL FOR PLAN/GRP        3

       

      INSURANCE PLAN/GRP (MCD)

                  TOTAL FOR PLAN/GRP     1078

       

      INSURANCE PLAN/GRP (MCDMC)

                  TOTAL FOR PLAN/GRP      931

       

      INSURANCE PLAN/GRP (NYPFC)

                  TOTAL FOR PLAN/GRP      370

       

      INSURANCE PLAN/GRP (OTH)

                  TOTAL FOR PLAN/GRP       49

       

                  TOTAL FOR LOCATION     2431

       

       

      LOCATION 04

      INSURANCE PLAN/GRP (CS)

                  TOTAL FOR PLAN/GRP        7

       

      INSURANCE PLAN/GRP (MCD)

                  TOTAL FOR PLAN/GRP      313

       

      INSURANCE PLAN/GRP (MCDMC)

                  TOTAL FOR PLAN/GRP      344

       

      INSURANCE PLAN/GRP (NYPFC)

                  TOTAL FOR PLAN/GRP       91

       

      INSURANCE PLAN/GRP (OTH)

                  TOTAL FOR PLAN/GRP       18

       

                  TOTAL FOR LOCATION      773

       

       

       

      LOCATION 05

      INSURANCE PLAN/GRP (CS)

                  TOTAL FOR PLAN/GRP        1

       

      INSURANCE PLAN/GRP (MCD)

                  TOTAL FOR PLAN/GRP      113

       

      INSURANCE PLAN/GRP (MCDMC)

                  TOTAL FOR PLAN/GRP       48

       

      INSURANCE PLAN/GRP (MMCFS)

                  TOTAL FOR PLAN/GRP        4

       

      INSURANCE PLAN/GRP (NYPFC)

                  TOTAL FOR PLAN/GRP        9

       

                  TOTAL FOR LOCATION      155

      /font[/quote]After setting up the template, the table is shown as below:

       

      [font="courier"]

      LOC     MCD     MCDMC     MMCFS     NYPFC     OTH     RES     CPP     CS     LOC TOTAL

      01     341     395     1     91     19                    847

      02     55     72     2     15     5     11          9     160

      03     1078     931     2X     370     49     11X     3     9X     2431

      04     313     344     2X     91     18     11X     3X     7     773

      05     113     48     4     9     18X     11X     3X     1     155

      /font[/quote]Please note the ones that I marked with "X". Those values are not from the data file. Instead, they are filled with the values from previous records.

       

      By the way, I set "Loc" as "Page Header", all the other fields as "Append" except "Loc Total" as "Detail". In "Field Properties", I selected "Leave empty cells blank" for every field.

       

      Does anybody have any idea why this happened? I'd really appreciate your help.

        • Null Field Filled with Previous Row's Value
          Data Kruncher

          Hi jw2523, and welcome to the forum.

           

          If you set the location template to be an append instead of a page header you should be alright.

           

          I don't have v7, but was able to get the table results you want with v9.

           

          V7 has a limit of 9 append templates so if all of the possible group codes are represented in your data, you should be OK.

          • Null Field Filled with Previous Row's Value
            jw2523 _

            Hi Data Kruncher,

             

            Thank you very much for your advice. I really appreciate it.

             

            Unfortunately, I tried to change the template for the location from Page Header to Append, but it still doesn't change anything. My guess is that v.7 and v.9 are different at this point, or the whole model settings you made are different from mine. What I did was that, for each insurance plan/group, I used both lines (INSURANCE PLAN/GRP... as the first line and TOTAL FOR PLAN/GRP... as the second line) as sample text, then use INSURANCE PLAN/GRP(...) as the trap for each insurance and then grab the number from the second line.

             

            Would you mind to let me knwo how you set up your model?

             

            Your help would be greatly appreciated.

            • Null Field Filled with Previous Row's Value
              joey

              If I am understanding your approach correctly, you are using the line "TOTAL FOR LOACTION" as your detail line, and the subtotals by plan type as appends, in addition to the location being an append.  So, you have an append for each plan type.

               

              The way that Monarch handles appends is that for each detail record, it scans up the report looking for the closest of each append.  So for LOC 04, even though there is no CPP record, Monarch will try use the record for LOC 03. 

               

              Notice that the first occurance of each insurance plan type that you used an append template for is blank until the first occurence.  After that, Monarch appended them down to detail records until the next instance of the plan.

               

              In short, append templates really aren't the way to go with this.  Let me share my solution:

               

              - Detail line should be the 2 lines of INSURANCE PLAN.  I set the trap on INSURANCE in the right position, and extracted the count and the plan type.  Then, I added an append template for the location.

               

              When I looked at my table, I found that the plan type was only 3 positions, and some were longer (some were shorter too).  I could have used a calculated field to strip off trailing spaces and the ")", but I chose a different approach.

               

              I modified the detail template to be a floating trap and added the trap criteria of the parenthasees.  I increased the size of the plan type field to 10, and this captured the field correctly.  My table looked like the following:

              [font="courier"]Location     Plan     Number

              01     MCD     341

              01     MCDMC     395

              01     MMCFS     1

              01     NYPFC     91

              01     OTH     19

              02     CS     9

              02     MCD     55

              02     MCDMC     72

              02     MMCFS     2

              02     NYPFC     15

              02     OTH     5

              02     RES     11

              03     CPP     3

              03     MCD     78

              03     MCDMC     931

              03     NYPFC     370

              03     OTH     49

              04     CS     7

              04     MCD     313

              04     MCDMC     344

              04     NYPFC     91

              04     OTH     18

              05     CS     1

              05     MCD     113

              05     MCDMC     48

              05     MMCFS     4

              05     NYPFC     9

                /font[/quote]To denormalize the table to give you the report you wanted, I created a summary.  Location and Plan type were the key fields, and the number is the measure field.  I changed the plan type column to display accross rather than down, and ended up with the following:

               

              [font="courier"] 

              Loc     CPP     CS     MCD     MCDMC     MMCFS     NYPFC     OTH     RES     SUM(Number)

              01               341     395     1     91     19          847

              02          9     55     72     2     15     5     11     169

              03     3          78     931          370     49          1431

              04          7     313     344          91     18          773

              05          1     113     48     4     9               175[/font][/quote]Hope my explanation of how appends work makes sense to you.

              • Null Field Filled with Previous Row's Value
                jw2523 _

                Hi Joey,

                 

                Thank you so much for your detailed explanation and great help.

                 

                I tried your approach and it worked.

                 

                Thanks again for your great advice!