10 Replies Latest reply: Jun 4, 2015 7:14 AM by Stefan Wühl RSS

    creating calculated fileds from 2 tables

    Andrew Thomas

      I have 2 tables in my load script that I want to create some calulated fields from. I have tried to create a resident load but it doesn't work

       

      This is what I have at the moment

       

       

      MLST:

      LOAD AUFPL as operationno,
      APLZL as counter,
      AUFPL+APLZL as opno,
      MLST_ZAEHL as milestoneno,
      Right (MLST_ZAEHL,5) as milestone,
      MLSTN as usage,
      TEDAT as scheduleddate,
      LST_ACTDT as actualdate,
      if((TEDAT>today()),TEDAT) as FutureMilestones,
      if(TEDAT>Today()and TEDAT <Today()+35,TEDAT)as '5WeekOverview',
      if(TEDAT>=WeekStart(today(),0,-1)-7 and TEDAT<=WeekStart(today(),0,-1),TEDAT)as PrevWk,
      if(TEDAT < Today() and LST_ACTDT = '',TEDAT) as CurrrentDelinquent



      FROM
      [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\MLST.QVD]
      (
      qvd)

      where MLSTN = 'N0003';

      left join
      LOAD ZZMLST_ZAEHL1 as milestoneno,
      ZZMLST_TEDAT1 as Customerbaseline

      FROM
      [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\AFVU.QVD]
      (
      qvd);

      Calctable:
      left join MLST:
      load
      actualdate,
      scheduleddate,
      customerbaseline,
      if(actualdate > customerbaseline and scheduleddate > customerbaseline and customerbaseline > '',scheduleddate) as completeddelinquent,
      if(actualdate = '' and scheduleddate > customerbaseline, scheduleddate) as projectdelinquent
      Resident MLST;