8 Replies Latest reply on Jan 8, 2015 10:28 AM by Simon Runc

    create dimension based on lookup table

    Tom Dusiod

      Hi

       

      I'd like to create a dimension which is based on a lookup of a subset of the existing data. In excel one could use the lookup function; is there an equivalent in Tableau?

       

      Concrete example, I'd to create a dimension called "Original office" to find out to which office a particular client belonged to in prior given year (say 2013) - i.e. in excel it would be:

       

      "office_in_2013" =IFERROR(VLOOKUP("Client","client/branch in 2013",2,0), "branch")

       

      "Category" = if does not exist in "client/branch 2013 table" then "NewBiz", else if office is the same then "renewed" else "moved"

       

      Would appreciate any hints on how to recreate this in Tableau

       

      Cheers

       

      Tom

       

      datadatadatadata<<how to calculate??>><<how to calculate??>>
      yearclientBranchPremiumoffice_in_2013Category
      2013AAsydney10sydneyrenewed
      2013BBsydney20sydneyrenewed
      2013CCsydney30sydneyrenewed
      2013XMelbourne11Melbournerenewed
      2014AAsydney10sydneyrenewed
      2014XMelbourne11Melbournerenewed
      2014YMelbourne22MelbourneNewBiz<< new client
      2014BBMelbourne20sydneymoved<<client moved branches
        • 1. Re: create dimension based on lookup table
          Simon Runc

          hi Tom,

           

          ...nice challenge! In short yes you can using table calcs. In the attached I've created the 2 new fields, although notice that my Category one for 2013 terms them all 'New Biz' as it can't find an occurrence of them in 2012 (so I think this is actually more accurate , as your one terms every one in 2013 Renewed, on the basis they are in 2013...self-fulfilling prophecy!!). However you could easily add an extra if to account for this using the FIRST() call.

           

          Hope how I've done it makes sense. I'm running each LOOKUP on year, and asking it to compare to the previous (-1).

           

          If you want a more detailed explanation, let me know.

          • 2. Re: create dimension based on lookup table
            Tom Dusiod

            Hi Simon, wow super quick thank you & right about the "newBiz" - it has to be new at some point.

             

            Could you please try to re-load the file, I would like to take a look but getting an error when opening it using 8.1

             

            Cheers

             

            Tom


            • 3. Re: create dimension based on lookup table
              Simon Runc

              ah I only have 8.3, and Tableau has no back compatibility. I've amended the XML (a bit naughty!) to think it's a 8.1 file (none of the functionality I've used is 8.3 specific), and then re-zipped, so it should work (all a twbx is a zipped folder containing the workbook and the data). Let me know if you can't open it, and I'll send over each calculation, so you can re-create it..

              • 4. Re: create dimension based on lookup table
                Tom Dusiod

                Hi Simon, hope you have some patience left as I was unable to open the file - I relabled the .zip to .twbx (cannot connect to database - invalid database name) also tried to run the .twb file (inside teh zip) and in both cases got errors.

                 

                Would you pleases end over the calcs instead?  Just to clarify, does this method create new Dimension, which one can then use in subsequent calcs?

                Thanks for your interest and help on this one - I've been stuck on it for a while,

                 

                tom

                • 5. Re: create dimension based on lookup table
                  Simon Runc

                  hi Tom,

                   

                  No problem. Here is a view of the output

                  LookUp.JPG

                   

                  The 2 new Formulas Tableau Original Office and Category Tableau, are as follows

                   

                  Tableau Original Office

                  IF ISNULL(LOOKUP(ATTR([Branch]),-1)) THEN ATTR([Branch])

                  ELSEIF ATTR([Branch]) = LOOKUP(ATTR([Branch]),-1) THEN ATTR([Branch]) ELSE LOOKUP(ATTR([Branch]),-1)

                  END

                   

                  Category Tableau

                  IF ISNULL(LOOKUP(ATTR([Client]),-1)) THEN 'New Biz'

                  ELSEIF ATTR([Branch]) = [Original Office Tableau] THEN 'renewed'

                  ELSE 'Moved'

                  END

                   

                  As these are Table Calcs the Partition and Addressing needs to be set to look back 1 year, and compare the values

                   

                  I set eaxch Table Calc up the same. I used advanced, and set like this

                   

                  LookUp Set Up 2.JPG

                  This means it's restarting every customer. as I'm not interested in Brand, Excel Category or Excel Original Office (they just happen to be fields in the Viz), in the the next screen I've set up as follows

                   

                  LookUp Set Up 1.JPG

                  by Only going down as deep as Year, I get the desired affect. Hope this all makes sense

                   

                  In terms of them being Dimensions you can use, it's a yes and no! You can use them, but any Viz would have to have Year and Client in the view (or detail shelf) so the calculations can work. Table calculation work over the viz, and not 'in the background' as Row Level calcs do. This is the same for aggregate calculations.

                   

                  Let me know if this is confusing and I'll direct you towards some good articles explaining it (and a lot better than me!!).

                  • 6. Re: create dimension based on lookup table
                    Jonathan Drummey

                    Also see Find Earliest/First/Least and/or Latest/Last/Maximum of a Field and Use that for Other Fields. Tableau devs definitely know about this as an issue.

                     

                    Also, there's a possible solution for this particular thread involving a modified self-data blend, where the blend is on the year but one data source offsets the year by one. This lets you get the prior year's information as a dimension or measure in the view (depending on exactly what you are trying to do) and avoid some of the complexities of table calculations.

                     

                    Jonathan

                    • 7. Re: create dimension based on lookup table
                      Tom Dusiod

                      Thanks Simon - that solves the problem though have to say it really wasn't obvious! WoW!

                       

                      Would be good to be able to do this in the background, hopefully some clues in Jonathan's post

                       

                      Thanks everyone,

                       

                      Tom


                      • 8. Re: create dimension based on lookup table
                        Simon Runc

                        hi Tom,

                        I hadn't thought of that solution from Jonathan Drummey...very nifty (but again Data Blended fields can be restrictive, depending on what you are doing, eg. measures always act as aggregated fields)

                         

                        Although wouldn't exclusively solve your issue, you could vote this idea up http://community.tableau.com/ideas/2177 which is basically allowing for aggregated calculation that operate regardless of the detail in the Viz (very much like Row Level calculations).

                         

                        What I've found, is that sometimes it's easier to do some back end data-reshaping/creation-of-aggregated-fields outside Tableau, and let Tableau do what it does best, Visualize. I've found that nearly everything is possible, but sometimes a bit of upfront work saves time (and trouble) in the long run.

                         

                        Here is a Link, with a load of other links! which take you through Table Calcs from Beginner to Zen!

                         

                        Want to Learn Table Calculations? Here’s How! | Drawing with Numbers