8 Replies Latest reply on Dec 2, 2013 9:39 AM by Matt Lutton

    mimic VLOOKUP function in Tableau

    Wendy Zhou

      Hi all,

       

      I had trouble to calculate fields based on conditions. which we normally done this in Excel by Vlookup function, however it appears didn't work well as we have to update vlookup files if something new came up.

       

      lan.png 

      I create calculate filed 'Calculate language',

      if contains([Campaign],'_FR_')

      then 'FRENCH'

      ELSE 'ENGLISH'

      END

       

      My problem is, how to define more than 1 rule of French, '_FR_'/ '_FR'/ '_fr_'/'_fr_' ?

       

      Thanks,

      Wendy

        • 1. Re: mimic VLOOKUP function in Tableau
          Matt Lutton

          Can you specify it as follows?  This should include any of the variations you mentioned--the dashes/underscores aren't needed, I don't think.


          if contains([Campaign],'FR') OR contains([Campaign],'fr')

          then 'FRENCH'

          ELSE 'ENGLISH'

          END


          Another option is to convert the entire field to uppercase or lowercase first, then write a simpler calculation to determine the language.  The calc for that is like:

           

          LOWER([Campaign]) // this makes the entire string lowercase .

           

          From there, it would just be:

           

          if contains([NewFieldName],'fr')

          then 'FRENCH'

          ELSE 'ENGLISH'

          END

           

          I hope this helps.  See attached workbook for first option.

          • 2. Re: mimic VLOOKUP function in Tableau
            Wendy Zhou

            Thank you Matthew! exactly what I'm looking for!

            • 3. Re: mimic VLOOKUP function in Tableau
              Matt Lutton


              Happy to help out!  Cheers.

              • 4. Re: mimic VLOOKUP function in Tableau
                Joshua Kennedy

                So by altering your formula just slightly I came up with this.

                 

                 

                Basically; give me the last 5 Characters of [Campaign], take the first 2/5 characters and UPPER them then check for FR.  Now admittedly this doesn't solve all possible scenarios but it does solve the basic issues from the sample that you have shown.  It falls apart when there is not 3 characters on then end.  In cases where F.R.O.N as an example would not work.   Again a basic work around and I would need to see the cases that aren't working to come up with something a bit more thorough.

                 

                Hope this helps for now!

                 

                Josh

                • 5. Re: mimic VLOOKUP function in Tableau
                  Toby Erkson

                  Hmm, I would do this (KISS):

                  IF CONTAINS( RIGHT( UPPER( [Campaign] ), 5), 'FR')

                  THEN 'FRENCH'

                  ELSE 'ENGLISH'

                  END

                   

                  If there's more than two languages then the SELECT...CASE function may be a better solution.

                   

                  Wendy, given your question about the different possibilities, that tells me your data isn't clean.  There needs to be a process that limits what data is accepted (data entry controls) or else cleans it up before it's consumed i.e., ETL.

                   

                  < edit > Just goes to show that if you give a problem to 10 programmers you'll get 10 different correct answers

                  • 6. Re: mimic VLOOKUP function in Tableau
                    Wendy Zhou

                    I see what  you trying to pull here. That's handy, however in my case, pattern and position are inconsistent.

                    • 7. Re: mimic VLOOKUP function in Tableau
                      Wendy Zhou

                      Hey Toby, yes you're definitely right that data set is not clean. This data is generate everyday and large amount. there're so many possibility for cleaning it up, I'm working with media team who produce these data. to see is that possible to keep 'Campaign' name consistent input. data cleaning is always headache and important.

                       

                      However, one thing keep me from doing perfect data cleaning is Time frame. It not allow me to take 4weeks for data cleaning, as I set up data automatic ETL to our server from Google and feed into Tableau everyday. But I'm so happy that I have Tableau, makes my life little bit easier.

                      • 8. Re: mimic VLOOKUP function in Tableau
                        Matt Lutton

                        Not sure how this was branched off previously... it was meant for this thread:

                        As long as the other country's don't include the combinations of characters specified, like 'fr' then my initial solution will work just fine.  However, if you had a country like 'Africa' (I know its a continent, but just illustrating a point), then my solution would not work well unless you build in logic to account for the 'fr' inside of 'Africa'.  If you do have more countries/text possibilities to sift through, then you can extend as others have shown you.  It all depends on the data you've got--but based on what was posted as a sample, the simple solution I provided will work.