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
