3 Replies Latest reply on Mar 2, 2012 12:16 PM by Lisa Maiuro

    eliminate duplicates

    Lisa Maiuro

      Hi, I want to calculate the patient days based on a single claim. However, my data file has multiple records that comprise a claim. So, for example I may have 3 records with the same begin date of services and end date of service for a single hospital stay and different values for other fields, e.g. diagnosis or amount paid.  I want to calculate the patient days, i.e. DATEDIFF('day',[BeginDOS],[EndDOS]) for just one record with a unique patient id and begin date comnbination per col C in the attachment. Any suggestions?

        • 1. Re: eliminate duplicates
          Andy Cotgreave

          Hi Lisa

          How's the attached? I created two datediff fields - one is the same as yours, but I use an AVG() aggregation instead of a SUM. The other is a datediff of the max start and end dates.

          This solution doesn't eliminate the duplicates, it works around them!



          1 of 1 people found this helpful
          • 2. Re: eliminate duplicates
            Lisa Maiuro

            Andy, thank you so much for looking at this!  This is great. My follow up question though is whether this works when I want to roll up the data by individual months and years.  In the file I attached I had 1 month of data but I actually have multiple months and want to report the data as shown in the pdf but with multiple months but not by the field for whihc I want a max value, i.e the. patient id date combo in col c. (There are too many values in the actual data of the pat id and date combo in col c to list individually and I am only interested in the aggregate value) .  I did not use the MAX function earlier because I thought it would take the max of all values and not just the max value within each  unique patient id and begin date combination per col C.


            Also, if I do this in Excel, i.e. remove the duplicate fields in Col. C, you can see I get a different answer 252 total patient days (cell L 182) rather than 287 total pateint days (pdf from Tableau file you sent). What do you think?  Thanks again.

            • 3. Re: eliminate duplicates
              Lisa Maiuro

              The problem with this solution is that it requires that you list each unique patient id to get patient days- easy enough-  but the aggregate does not reflect the sum of the values of the patient days for  the patient id. I have too many patient ids to list them all and am only interested in the sum of the patient days for each patient for each month.