5 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Unique Lines Question

    CK77 _

      VERSION 7


      Claim          Paid Date        Service #     Paid Amount

      A1234567     01/15/2008            1                   125.00

      A1234567     01/15/2008            2                      0.00


      Hi, I have a question related to my work and I would GREATLY appreciate any help that leads to a solution. So, thanks in advance. I have a list of 10,000 health insurance claims on which I must submit a DOI report. I need to break the data down into two lists; claims that have been paid and claims that have been completely denied. When I filter the data to look for unique claims I am getting paid claims in the denied list. Our claims can have up to 10 services under one claim. If 9 of those 10 services are denied and one is paid, it is still considered a paid claim. How can I separate the data into UNIQUE lists of:

      1. Claims that have at least one service covered.

      2. Claims that zero services covered.


      Thanks again in advance!!

        • Unique Lines Question
          Data Kruncher

          Hi CK,


          I recall that v8 introduced some (at the time) new ways to handle unique key values.


          Do you have the Standard version or the Pro version (in case we need to look at a workaround that may involve external lookups)?

            • Unique Lines Question
              CK77 _




                • Unique Lines Question
                  Data Kruncher

                  Good. Pro version. :cool:


                  So this is what I'm thinking...


                  First, define a PaidClaims filter limiting results to Paid > 0. Now build a summary. Set the default filter to your new PaidClaims filter, and use the Claim as your key field, and the sum of the Paid amount as your measure field. This is your list of unique claim number which have been paid.


                  Export the summary to an Excel file; we'll use this to determine which claims for which no services have been paid.


                  Now back in the table window build an external lookup to your freshly exported summary, bringing in the claim number as PaidClaim.


                  Now build a new calculated field, ClaimStatus with this formula:



                  If the IsNull function isn't available to you in v7, use this instead:



                  1/0 gives you the same result: a null value.


                  Now create an Unpaid filter using the ClaimStatus field, and again create a new Unpaid Claims summary listing the Unpaid filter as the default, Claim number as the key field, and the Paid amount as the measure. It doesn't really matter what the measure field is.


                  That should give you a unique list of unpaid claim numbers.


                  How does that work for you?



                    • Unique Lines Question
                      CK77 _

                      Hi, Kruncher.

                      Sorry I'm just now getting back. Been out of work sick. When I summarize my data it doesn't add up. For instance, there are 17,186 Unique Claim Numbers but when I followed the instructions this is what I got.


                      13,280 Paid Claims

                      4,105 Denied Claims


                      Is it possible some of the paid claims are still falling into the denied category because of the one service that is denied?



                        • Unique Lines Question
                          Grant Perkins



                          Am I right to assume that if a claim show any paid services amount anywhere it is paid but a zero total means it is not paid and THERE ARE NO OTHER CRITERIA or possible data oddities?


                          If so, extrapolating in my mind what an entire report might look like, I would be tempted to suggest you just grab each line as detail and the sum the 'paid' field in a summary with the key field of CLAIM NUMBER.


                          Anything that is zero is unpaid and therefore, I assume, a denied claim. Anything that is paid would be a paid claim.


                          Iirc you can filter on the measures in V7 in order to get a list of paid and unpaid.  If the numbers still don't add up it would suggest

                          there may be something odd in the data or the way the template/model is reading it. Might you be picking up lines that are not real detail records - continuation lines after page breaks where records cross over a page for example - or something like that?


                          In theory in the summary each Claim number should only appear once. So a summary with the claim number as key and a count measure should make the duplicates visible since the count would be greater than 1.


                          A few thoughts to play around with.