4 Replies Latest reply on Jan 21, 2019 12:03 PM by David Autry

    Lookup a value using LOD

    David Autry

      Hello all,

       

      Short version is ... I need to find a way to perform a lookup using LOD.  Table calculations are a no go as I have to set this up to work across views. Ultimately I need to populate every cell with the "Case Date" as it was at the time of "Changed Date".  Status changes may or may not have an associated "Case Date" so I have to find it ...

       

      I believe I need to pull the previous "Case_Date" --> New Value   OR the next "Case_Date" --> Old Value. 

       

      I've gotten as far as I can without help .. I'm not even sure if this is possible without Table Calculations in Tableau?  I understand the underlying problems are likely the the data source but I can't change it.  We've updated the workflow to always populate a Case_Date on a status change but I need to pull this for historical records (> 1000 cases) ... thus I need to find a LOD type solution even if it's Nth level calculations that get used.


      The calculated field I'm working with is "Case Date @ Time of Field Change" :

       

      IF { FIXED [Case ID] : SUM(IIF([Changed Field] = 'Case_Date',1,0))} = 0 
          // Case has no case date change
          THEN [Case Date - Initial]
      ELSE
          CASE [Changed Field]
              WHEN 'Case_Date' THEN DATE([New Value])
              WHEN 'Status' THEN
                  // LOD to changed date and pin status changes that have schedule changes to the new date
                  IFNULL({FIXED [Case ID],[Changed Date]:MIN(DATE([New Value]))},
                  
                  // Look backward for Case_Date - NEW VALUE
                  IFNULL(NULL,  // I need a LOD here that will find the previous Changed_Field = 'Case_date' and return [New Value]
                  
                  // Look forward for Case_Date - OLD VALUE                            
                  IFNULL(NULL, // I need a LOD here that will find the next Changed_Field = 'Case_date' and return [Old Value]
      
                  // End of the road  - Default to Case Date - Initial
                  DATE('JIBBERISH'))))            
              END
      END
      

       

      Attached is a sample workbook that contains a subset of data.

       

      Any help is appreciated.

       

      David

        • 1. Re: Lookup a value using LOD
          Zhouyi Zhang

          Hi, David

           

          Please find my calculation attached and let me know if my understanding is correct or not.

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Lookup a value using LOD
            David Autry

            ZZ - thank you for the reply.

             

            It's not quite what I need.  I don't think I can rely on MIN/MAX without an additional LOD because the 'Case Date' can move forward or backward in the timeline (case rescheduled to be earlier, case rescheduled to be later).

             

            I've added some visuals that I hope will help better explain things ...

             

             

            Where I have a NULL I need to find the most recent (by Changed Date) 'Case_Date' and extract the 'New Value' as a date.  Where no such prior 'Case_Date' exists ...  I need to use Case Date - Initial.

             

            Many thanks.


            David

            • 3. Re: Lookup a value using LOD
              Michel Caissie

              David,

               

              If you know the maximum number of Case_Date  that you can have per Case ID,  and this number doesn't get to high, here is what you can do.

              You will need a pair of lods  per number of Case_Date.  In your sample , the max is 2 , so I created two pairs.

               

              First you get the Changed Date  for each Case_Date

              CaseDateChangedDate1

              IFNULL(

                  {FIXED [Case ID] : MIN(if [Changed Field] = 'Case_Date' then [Changed Date] end)}

                  ,DATE(#3000-01-01#)

              )

               

              CaseDateChangedDate2

              IFNULL(

                  {FIXED [Case ID] : MIN(if [Changed Field] = 'Case_Date' and [Changed Date] > [CaseDateChangedDate1] then [Changed Date] end)}

                  ,DATE(#3000-01-01#)

              )

              etc,...

               

              Next you get the Value for each of those Dates

              NewValue1

              {FIXED[Case ID]:MIN( if [Changed Date] = [CaseDateChangedDate1] and [Changed Field] = 'Case_Date' then [New Value] end)}

               

              NewValue2

              {FIXED[Case ID]:MIN( if [Changed Date] = [CaseDateChangedDate2] and [Changed Field] = 'Case_Date' then [New Value] end)}

              etc,....

               

              and your final calculation would be

              if [Changed Date] < [CaseDateChangedDate1] then STR( [Case Date - Initial] )

              elseif [Changed Date] >= [CaseDateChangedDate1] and [Changed Date] < [CaseDateChangedDate2]  then [NewValue1]

              elseif [Changed Date] >= [CaseDateChangedDate2] //and [Changed Date] < [CaseDateChangedDate3]

                  then [NewValue2]

              //add as many elseif as you have  CaseDate

              end

               

              Michel

              1 of 1 people found this helpful
              • 4. Re: Lookup a value using LOD
                David Autry

                Michel,

                 

                I think that's the solution. 

                 

                Many thanks to ZZ and Michel for taking a look at this.

                 

                David