2 Replies Latest reply on Nov 2, 2016 6:09 PM by David Tabb

    Performing Vlookup's within 1 Data Source

    David Tabb

      Hello,

       

      My problem is that I need to compare 2 lists (i.e perform a vlookup) within 1 Data Source.... yes I know what your thinking.... vlookups... hasn't that been solved in many previous posts already......

       

      The reason for this post is that I can't seem to find any answered previous discussions in the Tableau forums that achieves this in 1 Data Source.

       

      I can only use 1 Data Source as it is Google Analytics (GA) data, ie all data is in 2 columns and I would need to repeat this process 20+ plus times for all the vlookups I need to do and don't want the mess, pain or risk of error that comes with 20+ Data Sources.

       

       

       

      Problem Breakdown

       

      I have created a dummy Excel Data Source (attached) that is similar to the GA Data Source for ease of this post. Below is a screen shot of the data. I have also attached the test Tableau workbook I am using.

       

      My goal: Use Tableau to work out if a user is a New User or Returning User based on how many Avatars they have created.

       

      Assumption: A New User is a User ID that has created 0 Avatars where a Returning User has created 1 or more Avatars.

       

       

      My Attempted steps to achieve this:

      1. I create a list of all unique User ID's, this is displayed on the attached Tableau workbook in sheet Home Screen Logins by User ID.
        • To get this list I have a Calculated Field called HomeScreenUserId that pulls the list of User ID's out of the Value column where the Action column = Home Screen.
      2. I create a list of Unique User ID's that have created an Avatar, this is displayed on the attached Tableau workbook in sheet Avatars Created By User ID.
        1. To get this list I have a Calculated Field called AvatarsCompletedUserId that pulls the User ID out of the Value column after the comma where the Action column = Avatar completed Successfully.
      3. What I want to do now is in sheet Home Screen Logins, have a boolean: NewUser? field that identifies Yes if a User ID has not created any Avatars and No if they have. I would do this in Excel by performing a vlookup on the AvatarsCompletedUserId Calculated Field however not sure how I achieve this in Tableau unless I create a duplicate Data Source.

       

      If you are able to help or have a better way to attack this problem I would be eternally grateful and can repay you in kind thoughts and good vibes

       

       

       

      Replicated GA dummy data:

        • 1. Re: Performing Vlookup's within 1 Data Source
          Stoyko Kostov

          Hi David - here's the approach that I took.

           

          First, I combined the 2 calculated fields you were creating - HomeScreenUserId and AvatarsCompletedUserId , into one, calling it simply UserId. The definition combines the definitions of the 2 fields like this:

           

          IF [Action] = 'Avatar completed Successfully' THEN

          trim((LEFT(

          MID([Value],

          FINDNTH([Value],',',1)+1),LEN([Value]))))

          ELSEIF  [Action] = 'Home Screen' THEN

          trim([Value])

          END

           

          Now, I created IsNewUser? with the following definition:

           

          IIF(SUM(IIF(Action=='Avatar Completed Successfully',1,0))>0,'No','Yes')

           

          The inner IIF ensures that UserIDs with avatar action are counted, and the others are not. The outer IIF assigns 'yes' to those with at least one avatar action, and 'no' to the rest.

           

          Please find the workbook attached. Hope this helps - let me know if you have any questions.

          1 of 1 people found this helpful
          • 2. Re: Performing Vlookup's within 1 Data Source
            David Tabb

            This is awesome!!!!! Thank you Stoyko, you have just made my day!