4 Replies Latest reply on May 11, 2016 7:52 AM by Mohit Sharma

    How to lookup and update a new column with a particular row value

    Mohit Sharma

      Hi,

       

      For creating a view, I need to lookup to an existing row's value and update a new column with its value accordingly. To give an idea here is my sample input:

       

      Product
      EventTime1
      A110 April
      A211 April
      A312 April
      A413 April
      B112 April
      B215 April
      B316 April

       

       

      The output with new column 'Time2' should like this:

       

      ProductEventTime1Time2
      A110 April10 April
      A211 April10 April
      A312 April10 April
      A413 April10 April
      B112 April12 April
      B215 April12 April
      B316 April12 April

       

      Based on my  filter of first event for every product, I need to update new column Time2 with values of Time1. Any kind of help is appreciated in creating calculated field for this. May be I am aware of built-in  functions in tableau which can make this lookup easy for me.

       

      Thanks,

       

      Mohit

        • 1. Re: How to lookup and update a new column with a particular row value
          Andrew Watson

          Try this formula for Time2:

           

          {FIXED [Product]:MIN([Time1])}

          • 2. Re: How to lookup and update a new column with a particular row value
            Mohit Sharma

            Hi Andrew,

             

            Thanks for your suggestion. I have already tried this LOD expression before posting this question. But it doesn't work on filter of getting Time1 value with min Event number. Rather it just takes minimum Time1 for every product directly. For ex:

             

            Product

            Event

            Time1

            Time2

            A110 April10 April
            A28 April10 April
            A312 April10 April
            A413 April10 April
            B112 April12 April
            B211 April12 April
            B316 April12 April

             

            Now the formula you gave me will fail as it will populate Time2 column with 8 April (Product A) and 11 April (Product B). I have tried this as well but it seems there is some mistake I am doing while calculating:

             

            IF { FIXED [Product]: MIN([Event])}= '1' THEN [Time1] END

             

            Thanks,

            Mohit

            • 3. Re: How to lookup and update a new column with a particular row value
              Andrew Watson

              Ok, requirements have altered somewhere vs the initial question :-)

               

              You want to return the Date of the First (Minimum) Event - which is not clear in your original table but becomes clear in the following post.

               

              This is more complicated - a combination of LOD calculation and advanced table calculation can be used to do this.

               

              First an LOD to identify the min event for each product and pulling out that event number to return the date of the min event: IF [Event] = {FIXED [Product]:MIN([Event])} THEN [Time1] END

               

              This will return the date for the min event and NULL for the non-min events. Next you want to populate the NULL values with the date of the min event - which means using a table calculation on the above calculated field. This formula will do it: WINDOW_MIN(MIN([MinEvent]))

               

              Set up your table calc Compute Using as follows and you can see the table returned. If you add/remove any columns the Compute Using will need to change to reflect it.

               

              1 of 1 people found this helpful
              • 4. Re: How to lookup and update a new column with a particular row value
                Mohit Sharma

                Thank you so much Andrew for the awesome response! It really helped.