12 Replies Latest reply on Jul 27, 2016 8:22 AM by swaroop.gantela

    Creating Calculated field that compares values from 2 data source and I can use as a filter

    Ben Rasilim

      Hi Tableau Community.

       

      I need help resolving this problem that I have and can't find the solution in this forum or online.

      Here is the case, I have sets of tables with 1 table contains fields: ID, Item, Value and another as the reference contain limit value for each item.

      below is the example of the tables I have, except I have 36million rows for table 1. (and ps, it's a tableau data extract)

      the problem is, I need to create a calculated field, classifying each transaction based on the value compares to the limit

       

      here is the example formula I used

      if [Value1] > ATTR([Reference].[Limit]) then 'class1' else 'regular' end

       

      but it's an error and to resolve the error I need to use

      if SUM([Value1]) > SUM([Reference].[Limit]) then 'class1' else 'regular' end

      to modify all value into aggregate so they say.

       

      but that doesn't allow me to use the field as a dimension, which is what I need, a dimension I can use as a filter or classification.

      what I need is basically a real vlookup that add an extra column to the table using using reference table.

      I know that I can achieve that if I just create the detailed table in the workbook, but I need it for each record and doing it to 36million data source is just not possible.

       

      attached is the example case

      Please provide me with a solution, I've had this problem for quite a while for a lot of my report.

      I need a way to do vlookup to a data extract

       

      Thanks you for your assistance

      Ben

       

      Example table 1 : Txn

      TranIDItem 1value1
      A01A10
      A02A26
      A03A13
      A04B20
      A05B13
      A06B14
      A07C10
      A08C22
      A09C16
      A10D15
      A11D21
      A12D17
      A13E14
      A14E29
      A15E21

       

      Example table 2: Reference

       

      Item 1limit
      A15
      B15
      C20
      D20
      E12