3 Replies Latest reply on Nov 1, 2016 2:57 PM by Lachlan MacGregor

    Trouble with Filtering by Calculated Fields created to turn Long/Thin Data to Wide

    Lachlan MacGregor

      Hi

       

      I have a large data set with data in a long, thin format with essentially just 3 columns:

       

      ID = grouping of fields

      Key = name of field

      Value = value of field

       

      For example a simplified version is:

      IDKeyValue
      1NameJohn
      1Sales100
      1Postcode2000
      2NameJames
      2Sales200
      2Postcode2001

       

      I have used calculated fields to convert the relevant fields so the data is Wide because I need to work with these fields in various ways that (I don't think) thin data will allow.

       

      This is the type of calculated field, though I have played with various formats and functions:

      Postcode = IF [KEY] == 'Postcode' THEN [VALUE] END

       

      When I try to use these fields as normal fields I run into some issues. For example if I want to filter by Postcode = 2000, the filter also filters out all the other Key data so I get null for Sales or Name.

       

      I feel I am missing something basic. Perhaps either I don't need to create the calculated fields in the first place (but then it is difficult to work with the data) or need to filter in a different way.

       

      I have searched high and low, but can't find many things dealing with this issue. Most talk about dates being Wide and converting to thin.

       

      At the end of this article it deals with reshaping from tall to wide, but doesn't address this issue:

      Data Prepping and Data Cleaning in Tableau Explained | Tableau Public

       

      I have attached a simple workbook to illustrate.

       

      Many thanks in advance for your help.

       

      Regards

      Lachlan