6 Replies Latest reply on Mar 17, 2016 11:47 AM by Adam Crahen

    how to use lookup function

    K P

      Hello Tableau Gurus,

      My data looks like this.

       

      ID 1     ID 2     Name   

      1         2          abc       

      2         3          xyz       

      3        null       pqr        

      4        1           efg       

       

      and I want to make a calculated field so data look like this.

       

      ID 1     ID 2     Name      name calculated field I want to show

      1         2          abc          xyz

      2         3          xyz          pqr

      3        null       pqr          null

      4        1           efg          abc

       

      So basically, I need to lookup the [ID 2] value in [ID 1] column and if that values exist in [ID 1] column show the name value of [ID 1]

       

      How do I do that?

       

      Thanks.

        • 1. Re: how to use lookup function
          Adam Crahen

          Hi Kush-

           

          Try this:

          Lookup

          IF index() = WINDOW_MAX(index())

          THEN LOOKUP(ATTR(Name),first())

          ELSEIF ISNULL(ATTR([Id 2]))

              THEN str(ATTR([Id 2]))

          ELSE LOOKUP(ATTR(Name),1)

          END

           

          9.2 attached.

          2 of 2 people found this helpful
          • 2. Re: how to use lookup function
            K P

            Hello Adam,

            Thank you very much.

            This works great.

            Thanks again.

            • 3. Re: how to use lookup function
              K P

              Hello Adam,

              What if ID 1 column value has multiple names in different rows? like this,

              ID 1     ID 2     Name  

              1         2          abc      

              2         3          xyz      

              3        null       pqr       

              4        1           efg      

              2        5          mno

               

              then I would like to see,

              ID 1     ID 2     Name      name calculated field I want to show

              1         2          abc          xyz

              1         2          abc          mno

              2         3          xyz          pqr

              2         5          mno        null

              3        null       pqr          null

              4        1           efg          abc

               

              because there are 2 different name values for value 2 in [id 1]

              and there is no 5 in column [id 1] so lookup says null

               

              May be your calculation already doing it and i might need to sort the output certain way.

              I am not sure.

              Thanks

               

              • 4. Re: how to use lookup function
                Adam Crahen

                The logic is currently pulling from the previous line, except the last line pulls from the first.  it looks like you want it to to be more based on ID 2 matching ID 1 and create new records??  I think I am now confused on your requirement.

                • 5. Re: how to use lookup function
                  K P

                  Hello Adam,

                  Sorry to confuse you with the requirement. I think I explained it wrong.

                  May be this can make it simple.

                   

                  if row data is this,

                  id1        id2            name

                  1           2               A

                  2          11              B

                  2          12              C

                  2          null            D

                   

                  then I would like,

                   

                  id1        id2            name          lookup

                  1           2               A                B

                  1           2               A                C

                  1           2               A                D

                  2          11              B               null

                  2          12              C              null

                  2          null            D              null

                   

                   

                  So for your question, yes it does look like I want to create records. sort of.

                   

                  Is this possible?

                   

                  I appreciate your time.

                   

                  thanks

                  • 6. Re: how to use lookup function
                    Adam Crahen

                    I can't think of a good way to do this with the single data source.