Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Script Help ???

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

Community Browser