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

    Unique Records in a Summary

    tsutter _

      Hello,

       

      I have a large report that runs daily(examples below).  The original report can have over 20 records per claim.  I use a number of different summaries with seperate filters to break the data down.

       

      The problem comes into play when a claim has multiple ppo's, st's, or HD codes.  This logicially forces the summary to split that claim onto two records.  My question is, is it possible to combine these records in a summary.  I use the Max command in SQL to do this, however that requires using subqueries and I have no luck trying to duplicate that logic within Monarch.

       

      I manually updated a second example to show exactly how I want it summarized.  I always want the record with the most data.

       

       

      CLM     CLM_AGE     PPO     STATUS     SYS_HD     USER_HD

      200804180548     76          7          

      200804180548     76     HLTHLINK     7          

      200804190199     75          6          

      200804190199     75     COALITN     6          

      200804190877     75          3          0030

      200804190877     75     ALLIANCE     3          0030

      200804210566     73          7          

      200804210566     73     PHCS     7          

      200804252293     69     PHCSHD     2     0028     

      200804252293     69     PHCSHD     5          /CODE

       

       

      What I want:

      CLM     CLM_AGE     PPO     STATUS     SYS_HD     USER_HD

      200804180548     76     HLTHLINK     7          

      200804190199     75     COALITN     6          

      200804190877     75     ALLIANCE     3          0030

      200804210566     73     PHCS     7          

      200804252293     69     PHCSHD     2     0028     

      /CODE

       

       

      Any help would be greatly appreciated.

       

      Thanks,

      Tim Sutter

        • Unique Records in a Summary
          Grant Perkins

          Welcome to the forum Tim.

           

          Would I be correct to assume that these summary lines are in effect direct transpositions from the table?

           

          If so I guess one approach would be to allocate a calculated field value to each table line based on which of the 3 fields is populated. That would give you a value of 0 through 3 perhaps. You could then filter the table or  summary for the max value and present only that line. Crude but simple and I assume that you would not expect records with the smae number of fields populated in this example.

           

          Note that I have not tried this in a test yet so I may have missed the mark.

           

           

          Grant

            • Unique Records in a Summary
              tsutter _

              Grant, Thank you for the quick reponse.

               

              However, I'm not quite following.  I don't think my explanation was sufficient.  Yes the summary lines are directly filterd and summarized from the table.

               

              The three fields can all have any number of 7 to maybe 100 different values.  They are also character fields.  I can not filter on the table, because each ColA can have multiple lines, each of which have dollar amounts that need to be summed, so every line must be taken into account in the summary, but I only want one record for each ColA value in the final output.  Some colums will contain null values.

               

               

              ColA   ColB   ColC  ColD

              aaa    a       a      a

              bbb    b       b      b

              ccc    c       1      1

              ccc    c       1     

              ddd    d       2      0

              ddd    d       7      0

               

              I would like to be able to reduce these down to one line, where the dollars are still being totalled, but having ColB,C,D etc pressed into one line without losing the dollar amount on that line.

               

              I hope this is makes more sense.

               

              Thanks again,

              Tim

                • Unique Records in a Summary
                  Grant Perkins

                  ColA ColB ColC ColD

                  aaa    a    a    a

                  bbb    b    b    b

                  ccc    c    1    1

                  ccc    c    1

                  ddd    d    2    0

                  ddd    d    7    0[/code]

                   

                   

                  I would like to be able to reduce these down to one line, where the dollars are still being totalled, but having ColB,C,D etc pressed into one line without losing the dollar amount on that line.

                   

                  /quote

                   

                   

                  Tim,

                   

                  Taking the lines above as I understand it you need to report these in a summary (to sum the dollar values) something like:

                   

                  ColA ColB ColC ColD

                  aaa    a    a    a

                  bbb    b    b    b

                  ccc    c    1    1

                  ddd    d    7    0[/code]

                   

                   

                  Is that about right?

                   

                  For the ddd line, how would you decide which combination of the ColB/ColC/ColD entries you wish to display? Both have similar values in all three columns - indeed they are identical in 2 columns but not the third. Do you simply need to create a situation where the highest values in each column are used?

                   

                   

                  Grant

                    • Unique Records in a Summary
                      tsutter _

                      Grant,

                       

                      That is correct - I would like the highest value from those columns while still summing the dollars in other columns.  In most cases the only difference will be one blank record, and a second record with a word.

                       

                      Thanks,

                      Tim

                        • Unique Records in a Summary
                          Grant Perkins

                          Tim,

                           

                          OK, the way I see this is that the 3 columns for which you need only the highest values are really for infromation and are not a component of the calculation, so it is probably best to work out what the values should be and then attach them to the results of the dollar calculation.

                           

                          I think the simplest way to do that would be to create a simple summary for each combination of ColA with Cols b, c and D

                           

                          So:

                           

                          ColA   Colb

                          ColA   Colc

                          ColA   Colc

                           

                          Sort the first column to group all equal values and the second column to give what you consider to me the highest values first using presumaoly a regular sort BUT, if that does not always apply, you can define a list of possible values and set their order and specify that is what you want to use for the sort order. (This is often used for sorting by month names for example.)

                           

                          In the resulting 2 column summary for the ColA fields set the properties to 'Suppress Duplicate Values.'.  The export the result for use as an external  lookup table You should end up with one populated line for each value of ColA plus some blank lines for lower values associated with Col A duplicates.

                           

                          Do this for all three columns.

                           

                          Normally at this point I would suggest producing the ColA single line per ColA value dollar valuation summary as well. Export that ready for use with a new Monarch model. Export might be to a fixed width columnar text file to be read as a report,  or perhaps to a csv file or Excel or Access for use as a database inout source. Your choice.

                           

                          Now open a new instance of Monarch and use the ColA/Dollar value export file as your input. Create 3 external lookups using the ColA values as the link key to bring in the Col b, c and d values. The resulting table, once the columns have been positioned as required in the table, should be what you need.

                           

                          As an alternative if you want to work with a single model I would imagine that you should be able add the Col b, c and d lookups into your original model, creating additional columns for to replace the originals for the final output.

                           

                          So with care to ensure that the lookup tables are for the current report (not a previous version!) you could load the report and model and perform the col b, c and d exports to populate the lookup tables with current report values and then refresh the lookups to give the b c and d values for the additional columns against each record in the table for the  b, c and d fields. Since all record will now have the same b, c and d values in the substitue fields you can create your summary, include the substitute b, c and d columns instead if the originals and drill up and down as much as you like. - all three columns will have a single effect. You might even use them as Item fields rather than Key fields to save some processing.

                           

                          Do these suggsetions help you to move forward?

                           

                          Sorry if it sounds complicated. Neither approach is complicated in reality but is perhaps more difficult to describe than it is to demonstrate. Let me know what is not clear and I will try to re-phrase it.

                           

                          HTH.

                           

                          Grant

                            • Unique Records in a Summary
                              tsutter _

                              Grant,

                               

                              Your solution works great.  Thanks a lot for your time and advice.

                               

                               

                               

                              Solution:

                               

                              I used two models - the first models creates a lookup table with the unique values.  The second (original model) uses external lookups (filter out the blank rows/"supressed duplicate values"), and then I replaced the columns as you described.

                               

                              Both models will be ran daily (update lookup table, and create new report)

                               

                               

                               

                              Tim Sutter