3 Replies Latest reply on Jun 30, 2016 11:54 PM by lei.chen.0

    Blending two data sets via a lookup table

    Omar Risk

      Dear community,

       

      I'm new to tableau (and databases in general) but I have been experimenting with it in the past few days. It is pretty powerful and the map visualisations are great.

       

      I'm finding it hard to do what I intended to do and would appreciate some help.

       

      I have two tables that I want to aggregate to calculate a rate i.e. [ x in table A / y in table B ] per geographic area. The issue is thus. Table A is organised by parliamentary constituency (henceforth PCon) and table B by local authority district (LAD). There are lookup tables available to relate PCon codes to LAD codes. In general there are more LADs than PCons. Sometimes there is overlap such that there are 1-1 relationships, sometimes Many-1, and sometimes 1-M.

       

      e.g:

       

      PCon-A includes LAD-1 and LAD-2

      LAD-2 is also partly in PCon-B

      PCon-B includes LAD-2 and LAD-3

       

      My thinking is that where such relationships exist, I could group at a higher level. I.e. in this example my aggregate would be (PCon A+B)/(LAD 1+2+3).

       

      How do I go about automating this kind of grouping?

       

      It's important that I maintain a link to the geographical codes so that I can later map the result.

       

      Any help would be appreciated!

       

      Thank you.