5 Replies Latest reply: May 15, 2014 10:00 AM by jw2523 _ RSS

    How to reserve a fixed format in the table?

    jw2523 _

      Dear Colleagues,

       

      I have a report file shown as below

       

      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

       

      -


       

      The table generated from this report looks like this:

      ______________________________________________________

      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

      -


       

      As you can see, the heading is insurance type and the first column is location.

       

      The issue I'm having now is that sometimes there is no patient at all at a specific location. For example, for location 01, it could be that there is not a single patient for the reporting month. Since I already have an Excel template used for data export, if one location is missing, every row will move up one line and the report will be messed up.

       

      Does anyone know how to reserver the position for the location 01 in this case so that my report will always keep the same format?

       

      I'd really appreciate your advice.

        • How to reserve a fixed format in the table?
          Data Kruncher

          Anytime you want a fixed format with a format you can count on, I'd split up the final report from the data created by Monarch. That is, don't try to force the structure Monarch will create based on the current period data. Instead, build a template that will read in the values that you export to a "temp" file with Monarch.

           

          Then you can build exactly the format you want, with known values for the location and insurance types. Then you can use either blanks or zeros when data does exist for that combination in that period (like location 01 type CCP).

           

          With such a small table, a formula that uses a combination Excel's VLOOKUP and MATCH functions , along with an IF(ISERROR(... will make short work of preparing your final report automatically for you.

           

          HTH,

          Kruncher

          • How to reserve a fixed format in the table?
            Steve Caiels

            [FONT=Calibri]Hi,[/FONT][/SIZE]

             

            [FONT=Calibri]If the source is a report or csv file (rather than Excel, Access etc) you can open an additional "master" report containing all locations. /FONT[/SIZE]

             

            [FONT=Calibri]For example, if the original report has location 02 missing[/FONT][/SIZE]

            [FONT=Calibri]location 01  /FONT[/SIZE]

            [FONT=Calibri]sample CPP   /FONT[/SIZE]

            [FONT=Calibri]sample OTH   /FONT[/SIZE]

             

            [FONT=Calibri]location 03  /FONT[/SIZE]

            [FONT=Calibri]sample OTH   /FONT[/SIZE]

            [FONT=Calibri]sample RES   /FONT[/SIZE]

            /code

             

            [FONT=Calibri]And you have a dummy report containing all locations with easily identifiable dummy data[/FONT][/SIZE]

            [FONT=Calibri]location 01          /FONT[/SIZE]

            [FONT=Calibri]sample zzz           /FONT[/SIZE]

             

            [FONT=Calibri]location 02          /FONT[/SIZE]

            [FONT=Calibri]sample zzz           /FONT[/SIZE]

             

            [FONT=Calibri]location 03          /FONT[/SIZE]

            [FONT=Calibri]sample zzz           /FONT[/SIZE]

            /code

             

            [FONT=Calibri]Opening them both together will result in a summary of[/FONT][/SIZE]

            [SIZE=3]location      CPP     OTH     RES     zzz     SUM(count)[/SIZE][/FONT]

            [SIZE=3]01            1       1       0       1       3[/SIZE][/FONT]

            [SIZE=3]02            0       0       0       1       1[/SIZE][/FONT]

            [SIZE=3]03            0       1       1       1       3[/SIZE][/FONT]

            /code

             

            [SIZE=3]I’m no Excel expert (unlike Kruncher!), but I’m guessing your template could ignore the zzz column?[/SIZE][/FONT]

             

            [SIZE=3]Hi Kruncher – See you in Phoenix.[/SIZE][/FONT]

             

            [SIZE=3]Regards,[/SIZE][/FONT]

            [SIZE=3]Steve.[/SIZE][/FONT]

              • How to reserve a fixed format in the table?
                Data Kruncher

                Great idea Steve. Could one not simply mock up a report that had every possible location code and every possible group code, and just have zeros for the values?

                 

                Then, as you suggest, open this "master" every time in conjunction with the "real" report.

                 

                This would get the desired final layout too.

                 

                Planning ahead this time, are you Steve?

                • How to reserve a fixed format in the table?
                  Grant Perkins

                  [SIZE=3][FONT=Calibri]Hi,[/FONT][/SIZE]

                   

                  [FONT=Calibri]If the source is a report or csv file (rather than Excel, Access etc) you can open an additional "master" report containing all locations. /FONT[/SIZE]

                   

                  [/SIZE][/quote]

                   

                  For a belt and braces approach it might be worth including all the possible insurance codes as well, perhaps for a non-existent location like "00" and then filter out Location 00 in the summary.

                   

                  Of course if the insurance codes don't now and never will present a problem this would be pointless.

                   

                  Nice idea Steve. Now, your solution for Excel/Access would be ...?  

                   

                  Maybe we should leave that one for Kruncher. :cool:

                   

                   

                  Grant