0 Replies Latest reply: Sep 23, 2015 5:03 AM by Amit Saini RSS

    Script Help ???

    Amit Saini

      Hi Folks,

       

      I don't have any option left except sharing with experts.  Below script is running fine based on business requirement , but only issue is taking around 2:30 Hrs to finish. Is there any way we can reduce this running time ???

       

       

      Qualify *;

      UnQualify '%*';

       

      Reason_Strichprobe:

      Load

      date(%Production_Date, 'DD-MM-YYYY') &  '$' & Applymap('MachineMap',Machine,'') & '$' & IF(TESTMachine = 4001 and TESTLine = 23001,'EUCD-2', Applymap('LineMap',%LineID, ''))& '$' & if(Holiday_Flag=1,WeekEnd_Shift,WeekDay_Shift ) & '$' &  Holiday_Flag  as %ProductionDate_Machine_Line_ID,

      date(%Production_Date, 'DD-MM-YYYY') &  '$' & Applymap('MachineMap',Machine,'') & '$' & IF(TESTMachine = 4001 and TESTLine = 23001,'EUCD-2', Applymap('LineMap',%LineID, ''))& '$' & if(Holiday_Flag=1,WeekEnd_Shift,WeekDay_Shift ) & '$' &  Holiday_Flag  as TEST_RS_ProductionDate_Machine_Line_ID,

      *;

      LOAD

          NLFDMASCHNR as Machine,

          Applymap('MachineMap',NLFDMASCHNR,'') & '$' & IF(NLFDMASCHNR = 4001 and NLFDLINIENR = 23001,'EUCD-2', Applymap('LineMap', if(NLFDLINIENR= 'B2e','B2E-2',

                                                                      if(NLFDLINIENR= '62Lst4','62LST4',

                                                                      IF( NLFDLINIENR = 'EUCD','EUCD-2',NLFDLINIENR))), '')) as %Machine_Line_ID,

          Applymap('MachineMap',NLFDMASCHNR,'') & '$' &  IF(NLFDMASCHNR = 4001 and NLFDLINIENR = 23001,'EUCD-2', Applymap('LineMap', if(NLFDLINIENR= 'B2e','B2E-2',if(NLFDLINIENR= '62Lst4','62LST4',NLFDLINIENR)), '')) as test_Machine_Line_ID,

          NLFDMASCHNR as TESTMachine,

          NLFDLINIENR as TESTLine,

          date(floor(DTTSPROBE), 'DD-MM-YYYY')& '#' & Applymap('MachineMap',NLFDMASCHNR,'') as %Opvolging_Key,

          Plant & '#' & NSPCIDNR & '#' & NTOOLNR  as %SPCIDREF_Key,

          Plant & '#' &SZUSINFO1 as %Tank_Key,

          date(floor(DTTSPROBE), 'DD-MM-YYYY') as DTTSPROBE,

          DTTSPROBE as DTTSPROBE_Org,

          Hour(DTTSPROBE) as %Hour,

          Hour(DTTSPROBE) as FromHour,

          Hour(DTTSPROBE) as ToHour,

          //If (hour(DTTSPROBE)>=6 or hour(DTTSPROBE)<= )

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,1,0)as Holiday_Flag,

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,Hour(DTTSPROBE),44) as %WeekEnd_Hour,

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,44,Hour(DTTSPROBE)) as %WeekDay_Hour,

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,'-',

                  if((Hour(DTTSPROBE)>=0 and Hour(DTTSPROBE) <= 5) or (Hour(DTTSPROBE)>=22 and Hour(DTTSPROBE)<=23 ),'N',

                      if(Hour(DTTSPROBE)>=14 and Hour(DTTSPROBE) <= 21,'L', 

                          if(Hour(DTTSPROBE)>=6 and Hour(DTTSPROBE) <= 13,'V')))) as WeekDay_Shift,

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,

              if((Hour(DTTSPROBE)>=6 and Hour(DTTSPROBE) <= 17),'V',

                  if((Hour(DTTSPROBE)>=18 and Hour(DTTSPROBE)<= 23) or (Hour(DTTSPROBE)>=0 and Hour(DTTSPROBE) <= 5),'N','-'))) as WeekEnd_Shift,   

          if (mixmatch(weekDay(Date(DTTSPROBE,'DD-MM-YYYY')),'Sat','Sun')<>0,

              if((Hour(DTTSPROBE)>=6 and Hour(DTTSPROBE) <= 17),'Early Shift', if((Hour(DTTSPROBE)>=18 and Hour(DTTSPROBE)<= 23) or (Hour(DTTSPROBE)>=0 and Hour(DTTSPROBE)<= 5),'Night Shift')),

                  if((Hour(DTTSPROBE)>=0 and Hour(DTTSPROBE) <= 5) or (Hour(DTTSPROBE)>=22 and Hour(DTTSPROBE)<=23 ),'Night Shift',

                      if(Hour(DTTSPROBE)>=14 and Hour(DTTSPROBE) <= 21,'Late Shift', 

                          if(Hour(DTTSPROBE)>=6 and Hour(DTTSPROBE) <= 13,'Early Shift',

                          )))) as Shift_Desc,           

          if (Hour(DTTSPROBE)<6,(date(floor(DTTSPROBE), 'DD-MM-YYYY')-1),date(floor(DTTSPROBE), 'DD-MM-YYYY')) as %Production_Date,

          NLFDMASCHNR as %MachineId,

          if(NLFDLINIENR= 'B2e','B2E-2',NLFDLINIENR) as %LineID,

          NLFDSTATIONNR as %StationID,

          applymap ('StationMap',NLFDSTATIONNR ) as Station_Group_Id,

          round(DXQ) as %Reason_Key ,

          round(DXQ) as DXQ_Int,

           Plant as Plant,

           NSPCIDNR as NSPCIDNR,

           NTOOLNR as NTOOLNR,

           NSTPNR as NSTPNR,

           DXQ as DXQ ,

           SZUSINFO1,

           SZUSINFO2,

           SZUSINFO3,

           SZUSINFO4,

           SZUSINFO5,

           SZUSINFO6,

           SZUSINFO7,

           SZUSINFO8,

           SZUSINFO9,

           NSTPFEHLERFLAG, 

           DTTSPROBE as Acquisition_Date,

          if(NSTPFEHLERFLAG=0, 'OK', 'NOK') as Acquisition_Status,

          if(NSTPFEHLERFLAG=0, 0, 1) as Acquisition_Status_No,

          SZUSINFO1 as Tank_SNr

          

       

      FROM

      $(vQVDSF_D)\$(vPlant)_Stichprobe.qvd (qvd)

      where not trim(SZUSINFO1) ='1234567011' and not trim(SZUSINFO1) = '1234567021' ;

      join

      LOAD

          FoutID as %Reason_Key,

          *

      resident LLFOUT where len(FoutID)= '6' ;

       

      drop Table LLFOUT;   

       

      UnQualify *;

       

      IntervalMatch(%WeekDay_Hour)

          Load Shift_Start_Time, Shift_End_Time

          resident Shift;   

         

      IntervalMatch(%WeekEnd_Hour)

          Load Shift_Start_Time_WE, Shift_End_Time_WE

          resident Shift_WE;

       

       

      Qualify *;

      UnQualify '%*';

      RENAME table Reason_Strichprobe to tmp_Reason_Strichprobe;

       

       

      Thanks,

      AS