4 Replies Latest reply on Oct 23, 2016 7:43 PM by Archie R

    Calculate field in one data source using lookup value from another

    Archie R

      This is my main data table. I want to do a lookup from another table and calculate sla. Then produce report on it.

          Main Source 1

      IDCurrent StageStage1 Start DateStage 2 Start Date
      1stage 110/20/2016
      2stage 11/1/2016
      3stage 11/2/2016
      4stage 22/1/2016
      5stage 22/3/201610/20/2016
      6stage 22/4/201610/20/2016
      7stage 22/5/201610/20/2016
      8stage 22/6/20163/1/2016
      9stage 22/7/20163/1/2016
      10stage 22/8/20163/1/2016


      Secondary Source 2 (SLA Source)

      Stage 15
      Stage 210


      I want to join Source 1 and 2 on Stage then have a Calc Field SLA in source 1 as below


      If stage = "Stage 1" AND Datediff(Stage 1 Start Date, Today()) > SLASOURCE.DAYS then "Outside SLA"

      ELSE IF Stage = "Stage 2" AND Datediff(Stage 2 Start Date, Today()> SLASOURCE.DAYS then "Outside SLA"

      ELSE "Inside SLA"



      Report should look like


      Calc Field SLA
      Count of ID
      Inside SLA4
      Outside SLA6



      Could you please suggest how to achieve this with 2 different sources?