13 Replies Latest reply on Aug 15, 2018 6:01 AM by Ken Flerlage

    VLookup with a Concatenation

    Marc Lane

      What I'm trying to do is to replace the content of two fields depending on the content of a 3rd.

       

         

      ABC
      1Column NameOld ValueNew Value
      2statusACTIVEDELETED
      3PROJECT_TYPE1139412038
      4PROJECT_TYPE1226112038
      5PROJECT_TYPE1203812261
      6statusACTIVEDELETED

       

      Where Column Name = PROJECT_TYPE, I want to vlookup on the following table:

       

       

      EFG
      1Profile IDFirst NameLast Name
      211394MarcLane
      312261JoeSmith
      412038JohnDoe
      5
      6

       

      But, I want to replace the 5 digit Profile ID with a concatenation of the First Name and Last Name fields from the second table. So, in the first table, where the first instance of PROJECT_TYPE under the Column Name column has an old value of 11394, the result would be Marc Lane would replace the 11394 under the Old Value column. However, if the entry under Column Name = anything other than PROJECT_TYPE, I would like the entry under the Old Value column to remain as is.. So where Column Name = status, old value would still = ACTIVE.

       

      The result would be a table that looks like this:

       

         

      IJK
      1Column NameOld ValueNew Value
      2statusACTIVEDELETED
      3PROJECT_TYPEMarc LaneJohn Doe
      4PROJECT_TYPEJoe SmithJohn Doe
      5PROJECT_TYPEJohn DoeJoe Smith
      6statusACTIVEDELETED

       

      Is this possible? If so, how would I go about this? Feels like it's sort of a conditional blending or something?

        • 1. Re: VLookup with a Concatenation
          Ken Flerlage

          Tableau works a bit differently than Excel, so we'll have to do something slightly different than what you've noted, but we'll get the same result. Whenever you need VLOOKUP-type functionality, that generally means you'll need a join. In your case, since you have the Old and New values that have to link to the other table, you'll need 2 joins.

           

          I put your 2 tables into a spreadsheet, then pulled them into Tableau:

           

           

          We will want to first join Old Value to Profile ID but Tableau believes Profile ID is a numeric field, so first click on that and change it to a string.

           

          Now set up the following. We want to left join to make sure we don't drop any records from the first table.

           

           

          Now drag Table2 over again and create a second join to Table1 based on New Value.

           

           

          It should now look like this:

           

           

          Now create 2 calculated fields:

           

          Old Value Final

          IF [Column Name]="PROJECT_TYPE" THEN

              [First Name] + " " + [Last Name]

          ELSE

              [Old Value]

          END

           

          New Value Final

          IF [Column Name]="PROJECT_TYPE" THEN

              [First Name (Table21)] + " " + [Last Name (Table21)]

          ELSE

              [New Value]

          END

           

          You can then build your table as follows:

           

           

          I've attached the spreadsheet and the packaged workbook. If this solves your problem, then please mark this as the "correct answer" so we can close the thread and others can learn from it in the future. Thanks!

          1 of 1 people found this helpful
          • 2. Re: VLookup with a Concatenation
            Marc Lane

            This definitely helped. And, I see what you're trying to accomplish. However, after I create the joins, I'm getting an error stating that the application is getting an error while communicating with the database. Oracle database error 1722: ORA-01722: invalid number. Not sure what that means?

            • 3. Re: VLookup with a Concatenation
              Marc Lane

              Figured it out! Thank you!

              • 4. Re: VLookup with a Concatenation
                Ken Flerlage

                Hmm. Any chance you could share some data or a packaged workbook?

                • 5. Re: VLookup with a Concatenation
                  Marc Lane

                  So, I basically gave you a simplified version of my problem. I had joined the Profile table elsewhere in my joins. Profile ID exists in the PROFILE_PREF table. There is a right join connecting Profile ID in PROFILE_PREP that joins to a Profile ID in PROJECT_AUDIT_LOG. And, there is a right join between the PROJECT_AUDIT_LOG and PROJECT_AUDIT_INFO as well, but it connects the Project # in each table, not a Profile ID. Still... the result appears to be correct.

                   

                  Capture.PNG

                  Old Value and New Value deminsions exist in the PROJECT_AUDIT_INFO table. Instead of joinging 2 additional PROFILE_PREF tables to PROJECT_AUDIT_INFO, I simply used the existing joined table. I also added in a ELSE IF statement to replace Null values with simple blank spaces. My calculation looks like this:

                   

                  IF [Column Name]="PROJECT_TYPE" THEN

                       [First Name] +" "+ [Last Name]

                  ELSEIF ISNULL([Column Name]) THEN

                       ""

                  ELSE

                       [Old Value]

                  END

                   

                  This calculation seems to provide the solution I needed.

                  • 7. Re: VLookup with a Concatenation
                    Marc Lane

                    Actually... I guess that didn't work. Looking at it more closely now. The issue when I try to join the two duplicate PROFILE_PREF tables to the AUDIT table is that the Profile ID in PROFILE PREF is in digits and the OLD VALUE and NEW VALUE values are strings. I think that's causing my issue. I tried converting the number to a string, but I still get the Oracle database error 1722: ORA-01722: invalid number error.

                    • 8. Re: VLookup with a Concatenation
                      Ken Flerlage

                      Can you go to Data then Convert to Custom SQL and share the resulting SQL statement?

                      • 9. Re: VLookup with a Concatenation
                        Marc Lane

                        SELECT "PROJECTS_INFO"."PROJECT_ID" AS "PROJECT_ID",

                          "PROJECTS_INFO"."PROJECT_TYPE" AS "PROJECT_TYPE",

                          "PROJECTS_INFO"."PROJECT_DESCRIPTION" AS "PROJECT_DESCRIPTION",

                          "PROJECTS_INFO"."PROJECT_DESTINATION" AS "PROJECT_DESTINATION",

                          "PROJECTS_INFO"."PROJECT_OWNER" AS "PROJECT_OWNER",

                          "PROJECTS_INFO"."IS_LOCK" AS "IS_LOCK",

                          "PROJECTS_INFO"."STATUS" AS "STATUS",

                          "PROJECTS_INFO"."NO_OF_RENEWAL" AS "NO_OF_RENEWAL",

                          "PROJECTS_INFO"."EXPIRED_TIMESTAMP_UTC" AS "EXPIRED_TIMESTAMP_UTC",

                          "PROJECTS_INFO"."CREATED_TIMESTAMP_UTC" AS "CREATED_TIMESTAMP_UTC",

                          "PROJECTS_INFO"."UPDATED_TIMESTAMP_UTC" AS "UPDATED_TIMESTAMP_UTC",

                          "PROJECTS_INFO"."UPDATED_BY" AS "UPDATED_BY",

                          "PROJECT_AUDIT_LOG"."PROJ_AUDIT_ID" AS "PROJ_AUDIT_ID",

                          "PROJECT_AUDIT_LOG"."PROJECT_ID" AS "PROJECT_ID (PROJECT_AUDIT_LOG)",

                          "PROJECT_AUDIT_LOG"."PROFILE_ID" AS "PROFILE_ID",

                          "PROJECT_AUDIT_LOG"."AUDIT_DESCRIPTION" AS "AUDIT_DESCRIPTION",

                          "PROJECT_AUDIT_LOG"."CREATED_TIMESTAMP_UTC" AS "CREATED_TIMESTAMP_UTC (PROJECT",

                          "PROJECT_AUDIT_LOG"."UPDATED_TIMESTAMP_UTC" AS "UPDATED_TIMESTAMP_UTC (PROJECT",

                          "PROJECT_AUDIT_INFO"."PROJ_AD_INF_ID" AS "PROJ_AD_INF_ID",

                          "PROJECT_AUDIT_INFO"."PROJ_AUDIT_ID" AS "PROJ_AUDIT_ID (PROJECT_AUDIT_I",

                          "PROJECT_AUDIT_INFO"."COLUMN_NAME" AS "COLUMN_NAME",

                          "PROJECT_AUDIT_INFO"."OLD_VALUE" AS "OLD_VALUE",

                          "PROJECT_AUDIT_INFO"."NEW_VALUE" AS "NEW_VALUE",

                          "PROFILE_PREF"."PROFILE_ID" AS "PROFILE_ID (PROFILE_PREF)",

                          "PROFILE_PREF"."USER_ID" AS "USER_ID",

                          "PROFILE_PREF"."FIRST_NAME" AS "FIRST_NAME",

                          "PROFILE_PREF"."LAST_NAME" AS "LAST_NAME",

                          "PROFILE_PREF"."EMAIL_ADDRESS" AS "EMAIL_ADDRESS",

                          "PROFILE_PREF"."PHONE" AS "PHONE",

                          "PROFILE_PREF"."COUNTRY_CODE" AS "COUNTRY_CODE",

                          "PROFILE_PREF"."ASSIGN_PROJ_EMAIL_NOTIFICATION" AS "ASSIGN_PROJ_EMAIL_NOTIFICATION",

                          "PROFILE_PREF"."ASSIGN_PROJ_TXT_NOTIFICATION" AS "ASSIGN_PROJ_TXT_NOTIFICATION",

                          "PROFILE_PREF"."REMOVE_PROJ_EMAIL_NOTIFICATION" AS "REMOVE_PROJ_EMAIL_NOTIFICATION",

                          "PROFILE_PREF"."REMOVE_PROJ_TXT_NOTIFICATION" AS "REMOVE_PROJ_TXT_NOTIFICATION",

                          "PROFILE_PREF"."NEW_TRNSFR_EMAIL_NOTIFICATION" AS "NEW_TRNSFR_EMAIL_NOTIFICATION",

                          "PROFILE_PREF"."NEW_TRNSFR_TXT_NOTIFICATION" AS "NEW_TRNSFR_TXT_NOTIFICATION",

                          "PROFILE_PREF"."COMPANY_NAME" AS "COMPANY_NAME",

                          "PROFILE_PREF"."IS_DELETED" AS "IS_DELETED",

                          "PROFILE_PREF"."CREATED_TIMESTAMP_UTC" AS "CREATED_TIMESTAMP_UTC (PROFILE",

                          "PROFILE_PREF"."UPDATED_TIMESTAMP_UTC" AS "UPDATED_TIMESTAMP_UTC (PROFILE",

                          "PROFILE_PREF"."PROFILE_TYPE" AS "PROFILE_TYPE",

                          "PROFILE_PREF1"."PROFILE_ID" AS "PROFILE_ID (PROFILE_PREF1)",

                          "PROFILE_PREF1"."USER_ID" AS "USER_ID (PROFILE_PREF1)",

                          "PROFILE_PREF1"."FIRST_NAME" AS "FIRST_NAME (PROFILE_PREF1)",

                          "PROFILE_PREF1"."LAST_NAME" AS "LAST_NAME (PROFILE_PREF1)",

                          "PROFILE_PREF1"."EMAIL_ADDRESS" AS "EMAIL_ADDRESS (PROFILE_PREF1)",

                          "PROFILE_PREF1"."PHONE" AS "PHONE (PROFILE_PREF1)",

                          "PROFILE_PREF1"."COUNTRY_CODE" AS "COUNTRY_CODE (PROFILE_PREF1)",

                          "PROFILE_PREF1"."ASSIGN_PROJ_EMAIL_NOTIFICATION" AS "ASSIGN_PROJ_EMAIL_NOTIFICATIO1",

                          "PROFILE_PREF1"."ASSIGN_PROJ_TXT_NOTIFICATION" AS "ASSIGN_PROJ_TXT_NOTIFICATION (",

                          "PROFILE_PREF1"."REMOVE_PROJ_EMAIL_NOTIFICATION" AS "REMOVE_PROJ_EMAIL_NOTIFICATIO1",

                          "PROFILE_PREF1"."REMOVE_PROJ_TXT_NOTIFICATION" AS "REMOVE_PROJ_TXT_NOTIFICATION (",

                          "PROFILE_PREF1"."NEW_TRNSFR_EMAIL_NOTIFICATION" AS "NEW_TRNSFR_EMAIL_NOTIFICATION ",

                          "PROFILE_PREF1"."NEW_TRNSFR_TXT_NOTIFICATION" AS "NEW_TRNSFR_TXT_NOTIFICATION (P",

                          "PROFILE_PREF1"."COMPANY_NAME" AS "COMPANY_NAME (PROFILE_PREF1)",

                          "PROFILE_PREF1"."IS_DELETED" AS "IS_DELETED (PROFILE_PREF1)",

                          "PROFILE_PREF1"."CREATED_TIMESTAMP_UTC" AS "CREATED_TIMESTAMP_UTC (PROFIL1",

                          "PROFILE_PREF1"."UPDATED_TIMESTAMP_UTC" AS "UPDATED_TIMESTAMP_UTC (PROFIL1",

                          "PROFILE_PREF1"."PROFILE_TYPE" AS "PROFILE_TYPE (PROFILE_PREF1)",

                          "PROFILE_PREF2"."PROFILE_ID" AS "PROFILE_ID (PROFILE_PREF2)",

                          "PROFILE_PREF2"."USER_ID" AS "USER_ID (PROFILE_PREF2)",

                          "PROFILE_PREF2"."FIRST_NAME" AS "FIRST_NAME (PROFILE_PREF2)",

                          "PROFILE_PREF2"."LAST_NAME" AS "LAST_NAME (PROFILE_PREF2)",

                          "PROFILE_PREF2"."EMAIL_ADDRESS" AS "EMAIL_ADDRESS (PROFILE_PREF2)",

                          "PROFILE_PREF2"."PHONE" AS "PHONE (PROFILE_PREF2)",

                          "PROFILE_PREF2"."COUNTRY_CODE" AS "COUNTRY_CODE (PROFILE_PREF2)",

                          "PROFILE_PREF2"."ASSIGN_PROJ_EMAIL_NOTIFICATION" AS "ASSIGN_PROJ_EMAIL_NOTIFICATIO2",

                          "PROFILE_PREF2"."ASSIGN_PROJ_TXT_NOTIFICATION" AS "ASSIGN_PROJ_TXT_NOTIFICATION 1",

                          "PROFILE_PREF2"."REMOVE_PROJ_EMAIL_NOTIFICATION" AS "REMOVE_PROJ_EMAIL_NOTIFICATIO2",

                          "PROFILE_PREF2"."REMOVE_PROJ_TXT_NOTIFICATION" AS "REMOVE_PROJ_TXT_NOTIFICATION 1",

                          "PROFILE_PREF2"."NEW_TRNSFR_EMAIL_NOTIFICATION" AS "NEW_TRNSFR_EMAIL_NOTIFICATION1",

                          "PROFILE_PREF2"."NEW_TRNSFR_TXT_NOTIFICATION" AS "NEW_TRNSFR_TXT_NOTIFICATION (1",

                          "PROFILE_PREF2"."COMPANY_NAME" AS "COMPANY_NAME (PROFILE_PREF2)",

                          "PROFILE_PREF2"."IS_DELETED" AS "IS_DELETED (PROFILE_PREF2)",

                          "PROFILE_PREF2"."CREATED_TIMESTAMP_UTC" AS "CREATED_TIMESTAMP_UTC (PROFIL2",

                          "PROFILE_PREF2"."UPDATED_TIMESTAMP_UTC" AS "UPDATED_TIMESTAMP_UTC (PROFIL2",

                          "PROFILE_PREF2"."PROFILE_TYPE" AS "PROFILE_TYPE (PROFILE_PREF2)"

                        FROM "REESE_OWNER"."PROJECTS_INFO" "PROJECTS_INFO"

                          LEFT JOIN "REESE_OWNER"."PROJECT_AUDIT_LOG" "PROJECT_AUDIT_LOG" ON ("PROJECTS_INFO"."PROJECT_ID" = "PROJECT_AUDIT_LOG"."PROJECT_ID")

                          LEFT JOIN "REESE_OWNER"."PROJECT_AUDIT_INFO" "PROJECT_AUDIT_INFO" ON ("PROJECT_AUDIT_LOG"."PROJ_AUDIT_ID" = "PROJECT_AUDIT_INFO"."PROJ_AUDIT_ID")

                          LEFT JOIN "REESE_OWNER"."PROFILE_PREF" "PROFILE_PREF" ON ("PROJECT_AUDIT_LOG"."PROFILE_ID" = "PROFILE_PREF"."PROFILE_ID")

                          LEFT JOIN "REESE_OWNER"."PROFILE_PREF" "PROFILE_PREF1" ON ("PROJECT_AUDIT_INFO"."OLD_VALUE" = "PROFILE_PREF1"."PROFILE_ID")

                          LEFT JOIN "REESE_OWNER"."PROFILE_PREF" "PROFILE_PREF2" ON ("PROJECT_AUDIT_INFO"."NEW_VALUE" = "PROFILE_PREF2"."PROFILE_ID")

                        • 10. Re: VLookup with a Concatenation
                          Ken Flerlage

                          Can you try running this against your database, perhaps in SQL Developer or another similar tool? I'd like to see if you get that error outside of Tableau.

                          • 11. Re: VLookup with a Concatenation
                            Marc Lane

                            Unfortunately, I cannot. I'm not a developer. I'm just a BA with access to the DEV DB who kinda knows how to use Tableau. I get the gist of joins and table combinations and building reports. But, I don't have any real SQL development/coding experience. I can try and ask one of my developers on the project to do so tomorrow. When I read more on the error it said to just export the whole database. I tried that but get the same error.

                            • 12. Re: VLookup with a Concatenation
                              Marc Lane

                              This time I was able to figure it out correctly. I used conditional joins where [New Value] = STR([Profile ID]). That appeared to give me exactly what I needed.

                              • 13. Re: VLookup with a Concatenation
                                Ken Flerlage

                                Fantastic!