2 Replies Latest reply on Jan 7, 2014 12:14 PM by Eric Munisteri

    vlookup off calculated field

    Frank Gary

      I'm trying to perform the equavalent of an Excel Vlookup in Tableau. I know the normal approach to this is a join prior to processing data however the value I want to lookup in my table is a calculated value. I am looking at securities data and each security has a rating and a BV. I use BV to weight each rating so that I can have a rating for a portfolio. This gives me a weighted average rating which I then want to lookup in a table to return a letter value.

      Rating_Weighting_Lookup.bmp

       

      So that I would have

      Portfolio     BV     Rating_Weighted     Rating_Letter

      Bob          14,813          10                    BBB-

      Jane         14,953          12                    BB

      Jim           15,030          12                    BB

       

      The letter values are much better for presentation but the number values were needed to perform the calculations.

       

      Is there a way to pull this off in Tableau?

        • 1. Re: vlookup off calculated field
          Mark Smith

          This is a good one...I think you're going to be limited because there isn't a way to access the other sheet as an object...its more of a database  environment.  Unless someone else has a better work around, I would create a calculated field off the [Rating_Weighted] column and use a case statement to pick the letter credit rating:

           

          Case [Rating_Weighted]

               When 1 Then 'AAA'

               When 2 Then 'AA+'

           

           

          End

          • 2. Re: vlookup off calculated field
            Eric Munisteri

            Mark is correct, but you have to account for fractions.  You could adjust the Calculated field for 'Rating_Weighted" to:

            sum([BV]*[Rating])/round(sum([BV])) and that would work, but if a value is 11.526, do you really want it to end up a 12 (BB+ to BB)?

             

            Another solution is to use the following calculated field (I called it "Letter Rating"):

            IF [Rating_Weighted] >= 0 AND [Rating_Weighted] <= .9 THEN "SP-1+"

            ELSEIF [Rating_Weighted] >=1 AND [Rating_Weighted] <= 1.9 THEN "AAA"

            ELSEIF [Rating_Weighted] >=2 AND [Rating_Weighted] <= 2.9 THEN "AA+"

            ELSEIF [Rating_Weighted] >=3 AND [Rating_Weighted] <= 3.9 THEN "AA"

            ELSEIF [Rating_Weighted] >=4 AND [Rating_Weighted] <= 4.9 THEN "AA-"

            ELSEIF [Rating_Weighted] >=5 AND [Rating_Weighted] <= 5.9 THEN "A+"

            ELSEIF [Rating_Weighted] >=6 AND [Rating_Weighted] <= 6.9 THEN "A"

            ELSEIF [Rating_Weighted] >=7 AND [Rating_Weighted] <= 7.9 THEN "A-"

            ELSEIF [Rating_Weighted] >=8 AND [Rating_Weighted] <= 8.9 THEN "BBB+"

            ELSEIF [Rating_Weighted] >=9 AND [Rating_Weighted] <= 9.9 THEN "BBB"

            ELSEIF [Rating_Weighted] >=10 AND [Rating_Weighted] <= 10.9 THEN "BBB-"

            ELSEIF [Rating_Weighted] >=11 AND [Rating_Weighted] <= 11.9 THEN "BB+"

            ELSEIF [Rating_Weighted] >=12 AND [Rating_Weighted] <= 12.9 THEN "BB"

            ELSEIF [Rating_Weighted] >=13 AND [Rating_Weighted] <= 13.9 THEN "BB-"

            ELSEIF [Rating_Weighted] >=14 AND [Rating_Weighted] <= 14.9 THEN "B+"

            ELSEIF [Rating_Weighted] >=15 AND [Rating_Weighted] <= 15.9 THEN "B"

            ELSEIF [Rating_Weighted] >=16 AND [Rating_Weighted] <= 16.9 THEN "B-"

            ELSEIF [Rating_Weighted] >=17 AND [Rating_Weighted] <= 17.9 THEN "CCC+"

            ELSEIF [Rating_Weighted] >=18 AND [Rating_Weighted] <= 18.9 THEN "CCC"

            ELSEIF [Rating_Weighted] >=19 AND [Rating_Weighted] <= 19.9 THEN "CCC-"

            ELSEIF [Rating_Weighted] >=20 AND [Rating_Weighted] <= 20.9 THEN "CC"

            ELSEIF [Rating_Weighted] >=21 AND [Rating_Weighted] <= 21.9 THEN "C"

            ELSEIF [Rating_Weighted] >=22 AND [Rating_Weighted] <= 22.9 THEN "D"

            ELSE "Unknown"

            END

             

            You could take the .9 and make them .9999999 if you wish.

             

            This calculated field may be too complex to run if you are attached to the excel spreadsheet live.  If that is the case, you would need to extract the data.

             

            See if the attached is what you are looking for.