Script Help ???

amit_saini — Wed, 23 Sep 2015 09:03:07 GMT

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,

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,

topic Script Help ??? in QlikView

Script Help ???