3 Replies Latest reply: May 15, 2014 9:55 AM by Gareth Horton RSS

    Multiple appends - missing data

    smaduro _

      I have a report with a detail template and 2 appends.  The template appears to be capturing all the data, but in Table view some pieces of data are missing.  The report consists of some patient info on one line, payment data (which can have one or more payments) below that, and charge data below that (again, can have one or more charges).  I created the detail at the bottom (capturing the charge info) and the 2 appends above it (one capturing payments and one capturing patient info).  I can get multiple charges, but if there is more than one payment, I only get one payment (the first one it finds) and the rest of the payments do not show up in the table. 

       

      [font="courier"]2415900382  PATIENT,ONE X         DOCTOR,CAT             05/25/2004                                                           

                  MEDICARE PART A PAY   I0001                         06/30/2004                       $41.38                              

      681  1001  OFFICE VISIT-LEVEL 3                                                    $63.50                                           

      2413400205  PATIENT,TWO           DOCTOR,ANDREW          05/05/2004                                                           

                  MEDICARE PART A PAY   I0001                         06/12/2004                      $126.63                              

                  MEDICARE PART A PAY   I0001                         06/12/2004                      -$63.41                              

                  MEDICARE PART A PAY   I0001                         06/05/2004                       $63.41                              

      744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

      744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

      744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

      744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

      744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

      881  1002  OFFICE VISIT-LEVEL 4                                                    $85.00                                           

      2415301079  PATIENT,THREE         DOCTOR,DEA             05/20/2004                                                           

                  MEDICARE PART A PAY   I0001                         06/25/2004                       $41.38                              

      881  1001  OFFICE VISIT-LEVEL 3                                                    $63.50                                           

      2330002082  PATIENT,FOUR          DOCTOR,ALAN            05/22/2003                                                           

                  BLUE CROSS PAYMENT    I0003                         06/19/2004                       $14.52                              

                  MEDICARE PART A PAY   I0001                         06/02/2004                       $58.09                              

      899  1048  ARTROCENTESIS, MAJOR                                                    $70.00                                           

      899  1002  OFFICE VISIT-LEVEL 4                                                    $80.00                                           

        /font[/quote]Patient One and Patient Three are fine, but Patient Two shows all the charges on separate rows but only one payment (the "bottom" one of $63.41) - and it repeats each time.  I can't get the $-63.41 or the $126.63 payments.  Same for Patient Four, I get both charges but only the "bottom" payment of $58.09 in the table.  Can anyone help?

        • Multiple appends - missing data
          DY*BIM _

          If you can send me a sample of a cleansed file and the model, I'll check it out. (you can mail me through the board.)

           

          Also, what version are you using?

           

          dy

           

          <<Edit typos>>

           

          [size="1"][ July 02, 2004, 05:36 PM: Message edited by: DY*BIM ][/size]

          • Multiple appends - missing data
            Grant Perkins

            Hi,

             

            I'm hoping you have the Pro version of Monarch at some level and with external lookup (Link) capability.

             

            If you do, then I think the most effective approach would be to make a model that looks at either the costs or the payments as detail lines, and appends the patient info from line 1.

             

            Set that out in a summary to the drill level you require - do you need the details of each charge/payment or does the total value work for you?

             

            Export the result to your preferred format.

             

            Return to your report and create another model picking as the detail whichever value you did not use last time. Create your table and then link to the external lookup file created in the first part of the process using the patient details as the link key.

             

            That's the simple description - but I do appreciate that it may get a little more complex than that depending on what you want the output to look like at the end.

             

            You may well be able to extract the information in one go using multi-row fields but would then face the complexity of manipulating those field to get the figures you require. I might be OK or it might not, depending on what you need to get to.

             

            Options to try clever tricks with templates having a variable number of rows (as yours do) using the 'preceding string' attribute for identifying the field do not seem viable in this example.

             

            If you don't have a PRO version a different approach will be required. In any event it looks like it would be useful to know what level of detail you may need to provide in the output since the charges and the payments seem not to be directly related to each other which may provide some interesting summary calculations!

             

            I hope this helps a little so far. dy's offer is worth considering up as well and if you have no problems in principle with providing the information I will also be quite happy to look at it.

             

            Have fun.

             

            Grant

            • Multiple appends - missing data
              Gareth Horton

              Hi

               

              There may be a simpler way of doing this if you use the Numeric OR trap.

               

              Think of what you are trapping as transactions, consisting of 1 detail (charges & payments) and 1 append (patient info)

               

              For the detail, use the Numeric OR by placing it over a position where a numeric will occur in either the charges or payments colums, then get the patient info as an append.

               

              Then, you will have a table that looks like this:

               

              [font="courier"] PATIENTID PATIENT NAME         DOCTOR          PATIENT_DA TYPE                 REF       DATE       CHARGE    PAYMENT

              2415900382 PATIENT,ONE X        DOCTOR,CAT      05/25/2004 MEDICARE PART A PAY  I0001     06/30/2004             41.38

              2415900382 PATIENT,ONE X        DOCTOR,CAT      05/25/2004 OFFICE VISIT-LEVEL 3                       $63.50         

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 MEDICARE PART A PAY  I0001     06/12/2004            126.63

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 MEDICARE PART A PAY  I0001     06/12/2004            -63.41

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 MEDICARE PART A PAY  I0001     06/05/2004             63.41

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 GROSS/MICRO, COMPLIC                      $135.00         

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 GROSS/MICRO, COMPLIC                      $135.00         

              2413400205 PATIENT,TWO          DOCTOR,ANDREW   05/05/2004 GROSS/MICRO, COMPLIC                      $135.00         

                /font[/quote]Then you could use the Fill Empty Cells Option on the Date column, if that is what is inferred by the report, I'm not sure.

               

              If you need the charges and payments broken out, you can use a filter with the IsNull() function on the Charges and Payments columns.

               

              Gareth

               

              Originally posted by smaduro:

              I have a report with a detail template and 2 appends.  The template appears to be capturing all the data, but in Table view some pieces of data are missing.  The report consists of some patient info on one line, payment data (which can have one or more payments) below that, and charge data below that (again, can have one or more charges).  I created the detail at the bottom (capturing the charge info) and the 2 appends above it (one capturing payments and one capturing patient info).  I can get multiple charges, but if there is more than one payment, I only get one payment (the first one it finds) and the rest of the payments do not show up in the table. 

               

              [font="courier"]2415900382  PATIENT,ONE X         DOCTOR,CAT             05/25/2004                                                           

                          MEDICARE PART A PAY   I0001                         06/30/2004                       $41.38                              

              681  1001  OFFICE VISIT-LEVEL 3                                                    $63.50                                           

              2413400205  PATIENT,TWO           DOCTOR,ANDREW          05/05/2004                                                           

                          MEDICARE PART A PAY   I0001                         06/12/2004                      $126.63                              

                          MEDICARE PART A PAY   I0001                         06/12/2004                      -$63.41                              

                          MEDICARE PART A PAY   I0001                         06/05/2004                       $63.41                              

              744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

              744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

              744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

              744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

              744  2536  GROSS/MICRO, COMPLIC                                                   $135.00                                           

              881  1002  OFFICE VISIT-LEVEL 4                                                    $85.00                                           

              2415301079  PATIENT,THREE         DOCTOR,DEA             05/20/2004                                                           

                          MEDICARE PART A PAY   I0001                         06/25/2004                       $41.38                              

              881  1001  OFFICE VISIT-LEVEL 3                                                    $63.50                                           

              2330002082  PATIENT,FOUR          DOCTOR,ALAN            05/22/2003                                                           

                          BLUE CROSS PAYMENT    I0003                         06/19/2004                       $14.52                              

                          MEDICARE PART A PAY   I0001                         06/02/2004                       $58.09                              

              899  1048  ARTROCENTESIS, MAJOR                                                    $70.00                                           

              899  1002  OFFICE VISIT-LEVEL 4                                                    $80.00                                           

                /font[/quote]Patient One and Patient Three are fine, but Patient Two shows all the charges on separate rows but only one payment (the "bottom" one of $63.41) - and it repeats each time.  I can't get the $-63.41 or the $126.63 payments.  Same for Patient Four, I get both charges but only the "bottom" payment of $58.09 in the table.  Can anyone help? /b[/quote]