5 Replies Latest reply on Jul 20, 2016 5:20 PM by Tien Nguyen

    Lookup Function - returning another dimension

    Jackie Kinney

      Hello there,

       

      Due to data confidentiality, I have created a simple data set below that illustrates my task at hand. Based upon three dimensions, I would like to create a new calculated dimension.

       

      Current table:

      Invoice NumberCommodityInvoice Count (aka % of Line Items with designated commodity for single invoice)
      1000Office Supplies0.8
      1000IT Hardware0.2
      1001Office Supplies0.1
      1001IT Hardware0.2
      1001Office Furniture0.7

       

      The heart of an issue is that one invoice can comprise of multiple commodities. I need to simplify to a relationship of 1 invoice to 1 commodity. I would like to create a calculated dimension- Adjusted Commodity - that follows the following logic. For every invoice number, lookup the max(Invoice Count) and return the Commodity.

       

      Desired output:

      Invoice NumberAdjusted Commodity
      1000Office Supplies
      1001Office Furniture

       

      I have not been able to figure out how to use the lookup function to return another dimension. I have tried using an IF statement but with no success.

       

      Any suggestions?