3 Replies Latest reply on Apr 7, 2018 4:10 PM by Ken Flerlage

    Overcounting issues

    TheCefi Org

      Hi All,

       

      I'm starting with tableau and I don't find a way to match my data after many hours of hard work.

       

      I have several sheets with data per administrative area, one sheet with the population (without date) and one SHP file.

       

      I'm looking for the best way to join or blend data but i'm facing problem for any solution.

       

      For Data Blending, I can't find a way to make advanced calculations. (see current discussion for issue on Data Blending)

       

      In this discussion, I'd like your advises on how to resolve overcounting:

      • 17,165 access point instead of 89
      • 835,142,042 inhabitants instead of 7,239,982

       

      As a result I try to calculate the numer of access point for 1,000 inhabitants:

      Number(Name of access point)  / ( SUM(Pop 2011) / 10000)

       

      Before making the calculation I need to have reliable data.

       

      Could you please help me to resolve the overcounting problem?

       

       

        • 1. Re: Overcounting issues
          Sasha Hanna

          Hi Cefi Org,

          So in the current set up, you are getting duplication because of the many-to-many relationship of the region/district/province, you could do the inner join to obtain the correct access point value:

          However the population seems lower, probably because of a name mismatch in one of the joining fields.

          Hope this helps a little.

          Cheers,

          Sasha

          • 2. Re: Overcounting issues
            TheCefi Org

            Hi Sasha,

            I tried all possible join configuration (including inner join) but I still have my overcounting problem.

            Is there a way to resolve the overcounting problem with joins or should I use Data Blending?

            Thanks

            • 3. Re: Overcounting issues
              Ken Flerlage

              Changing CNT to COUNTD (count distinct) should resolve the first problem, but you definitely have a bigger problem on your hand. I'd really suggest that you take a step back and explain to us what you are trying to do here. Provide some details on each of the tables you're joining, along with what exactly you're trying to do. Then we'll be better informed to provide the best solution.