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
      Days
      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"

      END

       

      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?