10 Replies Latest reply: May 15, 2014 10:01 AM by Bruce _ RSS

    Help with adding a ranking within a group of records

    Karen_VB _

      I have a text file that looks like this:


      ID 12345678       MAN, SUPER  Discharge 03/25/2020                                                                               

      ICD-9-CM INFORMATION:                                                                               

      MDC                          8     MUSCULOSKELETAL SYSTEM CONN TISSUE           

         DRG                          470     Major joint replacement or reatt                                                                               

      ICD-9-CM Diagnoses             POA                                                                               


         250.00 DMII WO CMP NT ST UNCNTR Y  

         272.0  PURE HYPERCHOLESTEROLEM  Y                                               

         401.9  HYPERTENSION NOS         Y                                               

         355.8  MONONEURITIS LEG NOS     Y                                               

         V14.8  HX-DRUG ALLERGY NEC      E                                               

         530.81 ESOPHAGEAL REFLUX        Y                                               


      ID 34526578       WOMAN, SUPER  Discharge 02/26/2058                                                                               

      ICD-9-CM INFORMATION:                                                                               

      MDC                          11     KIDNEY & URINARY TRACT                      

         DRG                          658     Kidney & ureter procedures for n                                                                               

      ICD-9-CM Diagnoses             POA                                                                               

      189.0  MALIG NEOPL KIDNEY       Y     

         401.9  HYPERTENSION NOS         Y                                               

         V45.89 POST-PROC STATES NEC     E                                               

         414.01 CRNRY ATHRSCL NATVE VSSL Y                                               

         V45.81 AORTOCORONARY BYPASS     E                                               

         443.9  PERIPH VASCULAR DIS NOS  Y /B  


         I need my finished table to look like this, ranking the diagnoses in the order they appear in the file, starting over counting at 1 when it is a new patient.      



      12345678     715.15     1

      12345678     250         2

      12345678     272         3

      12345678     401.9       4

      12345678     355.8       5

      12345678     V14.8       6

      12345678     530.81     7

      34526578     189         1

      34526578     401.9       2

      34526578     V45.89     3

      34526578     414.01     4

      34526578     V45.81     5

      34526578     443.9       6



      :confused:Any ideas about how I could do this? Thank you! :rolleyes:

        • Help with adding a ranking within a group of records
          Data Kruncher

          Hi Karen, and welcome to the forum.


          Unfortunately, because Monarch doesn't allow one to perform calculations based on other records, only with values within the same record, this type of requirement really can't be done within a single step or single model solution.


          So without getting tricky with multiple passes and some kind of external lookup tricks, the easiest approach I can recommend for you involves a Excel/Monarch combination, which won't shock many around here I'm sure.


          Once you model your file, just get only your two fields ID# and ProcedureCode (?) visible in the table, and export the table to an Excel file.


          Open the Excel file, and add your Rank as Column C. Put 1 in C2, and =IF(A3=A2,C2+1,1) in C3. Copy that down for all of your records.


          Now you should be able to export to a format which will be acceptable for your other system.


          While not the ideal solution, does it help at all?



            • Help with adding a ranking within a group of records

              I think Kruncher's solution is probably one of the easiest, but there are others.   I'd like to suggest another possibility or two.


              You could take your existing model and add a calculated field using either the Rowno() function, which returns the row number in the table.  This should match your report, assuming you don't use any filters.


              The downside about the rowno function is you can't use it in summaries, because it can chagne each time.  If you want to be able to summarize the ranking column, you'll need two passes.

            • Help with adding a ranking within a group of records
              Gerhard _

              Hi Karen, just saw your post.


              Seems that there is a simple solution in this case - with only one model.


              1. Take over the diagnosis code (?) example "715.15" with the detail template. I used the LSplit() to extract the left part.


              2. Add a calculated field "Ranking" with Line()-9


              3. Take over the patient ID with a header template. This will cause a page break for any new patient.


              The ranking field with the Line() function should show the correct numbering now.


              Hope this is the solution you are looking for.