1 Reply Latest reply on Feb 24, 2016 8:23 AM by Yuriy Fal

    SAS Files + Unique IDs = Duplicates

    Juan Barnett

      FARS.JPG

       

      I'm very new to Tableau so please have patience. I've done some research on the issue I'm having and I've yet to find a good solution.

       

      The SAS data set is from a federal agency and is a look at fatality information for 2014. The agency uses the Consecutive Number as the Unique ID for each data set.

       

      When I inner join the data based on Consecutive Number it causes duplicates rows to be counted in worksheets.

       

      Question: What is the best way, given that data sets that I'm currently working with, to ensure that any of my results are free of duplicate counts?

       

      Example: The count of vehicles should be ~43,000, but when I join the other data sets I'm getting a Count of Consecutive Numbers for all three data sets, which results in a total that is x3 what it should be.

       

      FARS 2.JPG

        • 1. Re: SAS Files + Unique IDs = Duplicates
          Yuriy Fal

          Hi Juan,

           

          Could you please change the aggregation

          from COUNT([Consecutive Number])

          to COUNTD([Consecutive Number])

          and see if results would be closer to expected?

           

          I suppose that even if your accident table

          contains one row per unique Consecutive Number,

          your other tables (vehicle and person) may contain

          more than one row per unique Consecutive Number.

          That is if more than one vehicle (or person) is involved in accident.

           

          That could lead to a finer granularity of your resulting dataset,

          namely one row per unique accident - vehicle - person combination.

           

          If that's the case, then you're better to use

          distinct counts on some of your Measures,

          or better to use Level-Of-Detail (LOD) calculations.

           

          Hope it could help.

           

          Yours,

          Yuri

          1 of 1 people found this helpful