topic Data Time Processing in the Second pass Load on Resident Data. in QlikView

Data Time Processing in the Second pass Load on Resident Data.

Tue, 19 May 2009 23:51:48 GMT

Hi Team ,

OverView

I have Developed a QVD File (E151_QVD.qvd , size : 261MB , Records : 4 MM ) .

This QVD file gets populated from a Load QVW file ( Pull_Evenet_151.qvw ) , Scheduled to run every day at 12 PM in the Windows Server.

I am checking a Time Stamp Field called CRTDATE . I fetch the Max date from the QVD file and pull the rest data after that date (CRTDATE) from Oracle database.

I am also pulling 3 extra fields called Year , Month , Day from the database by the to_Char method of Oracle on one of the Table Fields called EVENT_TIME. I am pulling it to show a uase friendly calendar in the QVW report File (Event_151.QVW)

Once Pulled the data from the database i concatenate it with the Existing data in the QVD file.

and store it back in the QVD file.

Problem : It seems that Qlikview when Loads the data from the QVD file and concatenates it to the pulled data from Oracle is very slow. I do not understand , if there is any performance issue in my Script.

Please find my attached QVW file.

Regards

Debashish.

Data Time Processing in the Second pass Load on Resident Data.

disqr_rm — Wed, 20 May 2009 00:07:07 GMT

Do you see "QVD Optimized" when it's loading from QVD file?

Data Time Processing in the Second pass Load on Resident Data.

Wed, 20 May 2009 00:38:54 GMT

No I do not see the QVD Optimized .

It shows

QVTable << E151_QVD 3,167,507

How should i make the QVD optimized ??

Regrads

Debashish

Data Time Processing in the Second pass Load on Resident Data.

disqr_rm — Wed, 20 May 2009 00:55:57 GMT

When you are loading from QVD, load it as same field names as in QVTable:

LOAD J_NO as OASIS.INSTRUMENT. J_NO ......

And it's always a good practice to mention which table to concatenate: CONCATENATE (QVTable) LOAD .....

Data Time Processing in the Second pass Load on Resident Data.

Wed, 20 May 2009 01:07:22 GMT

Hi Rakesh ,

I could not get the Solution.Can u please elaborate Sir.

Before that i want to mention that , when i load just the QVD file from a diffrent QVW file , i am able to see the " QVD Optimized " .

//---------------------------------------------ONLY THIS SCRIPTS RUN and Shows QVD Optimized.

load J_NO,CYCLE_NO,ZIP_FILE_ID,LEVENTID,IEVENTTYPE,EVENT_TIME,EVENT_TIME_NANOSEC, LASSAYNUMBER,CRTDATE,P1_TIME_STAMP,LRESULTID,DASSAYNUMBER,SREPINDEXINTEST,P69_DESTINATION_SLOT_NUMBER,D111_STATUS, P144_CAL_KIT_BOTTLE_NUMBER_1_F,LCALGROUPRUNID,P335_CURRENT_STEP_COUNT_DEVIAT,P358_SUPPLY_NUMBER,P556_STEP_COUNT_DEVIATION_LIMI,[Year],[Month] ,[Day]

from E151_QVD.qvd (qvd);

//---------------------------------------------

Data Time Processing in the Second pass Load on Resident Data.

disqr_rm — Wed, 20 May 2009 01:23:56 GMT

Try this - just for the first 3 fields and let me know what you see. If works, do the same for all the fields:

QVTable:
LOAD OASIS.INSTRUMENT.J_NO as J_NO,
OASIS.EVT_EVENT.CYCLE_NO as CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID as ZIP_FILE_ID;
SQL select OASIS.INSTRUMENT.J_NO,
OASIS.EVT_EVENT.CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID,
OASIS.EVT_EVENT.LEVENTID,
OASIS.EVT_EVENT.IEVENTTYPE,
OASIS.EVT_EVENT.EVENT_TIME,
OASIS.EVT_EVENT.EVENT_TIME_NANOSEC,
OASIS.EVT_EVENT.LASSAYNUMBER,
ECON.ZIP_FILE.CRTDATE,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'YYYY') Year,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'MON') Month,
TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'DD') Day,
max(case when IPARAMETERTYPE=1 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P1_TIME_STAMP,
max(case when IPARAMETERTYPE=2 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) LRESULTID,
max(case when IPARAMETERTYPE=3 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) DASSAYNUMBER,
max(case when IPARAMETERTYPE=4 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) SREPINDEXINTEST,
max(case when IPARAMETERTYPE=69 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P69_DESTINATION_SLOT_NUMBER,
max(case when IPARAMETERTYPE=111 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) D111_STATUS,
max(case when IPARAMETERTYPE=144 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P144_CAL_KIT_BOTTLE_NUMBER_1_F,
max(case when IPARAMETERTYPE=259 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) LCALGROUPRUNID,
max(case when IPARAMETERTYPE=335 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P335_CURRENT_STEP_COUNT_DEVIAT,
max(case when IPARAMETERTYPE=358 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P358_SUPPLY_NUMBER,
max(case when IPARAMETERTYPE=556 then OASIS.EVT_EVENT_PARAMETER.VPARAMETERVALUE else null end) P556_STEP_COUNT_DEVIATION_LIMI
from OASIS.EVT_EVENT_PARAMETER,OASIS.EVT_EVENT,OASIS.INSTRUMENT,ECON.ZIP_FILE
where ECON.ZIP_FILE.CRTDATE <= sysdate
AND ECON.ZIP_FILE.CRTDATE > to_date('$(MostRecentUpdate)','YYYYMMDD HH24:MI:SS')
AND ECON.ZIP_FILE.ZIP_FILE_ID = OASIS.EVT_EVENT.ZIP_FILE_ID
AND ECON.ZIP_FILE.ZIP_FILE_ID = OASIS.EVT_EVENT_PARAMETER.ZIP_FILE_ID
AND OASIS.EVT_EVENT.INSTRUMENT_ID = OASIS.EVT_EVENT_PARAMETER.INSTRUMENT_ID
and OASIS.EVT_EVENT.CYCLE_NO = OASIS.EVT_EVENT_PARAMETER.CYCLE_NO
and OASIS.EVT_EVENT.LEVENTID = OASIS.EVT_EVENT_PARAMETER.LEVENTID
and OASIS.INSTRUMENT.INSTRUMENT_ID = OASIS.EVT_EVENT.INSTRUMENT_ID
and OASIS.EVT_EVENT.IEVENTTYPE = 151
group by OASIS.INSTRUMENT.J_NO,
OASIS.EVT_EVENT.CYCLE_NO,
OASIS.EVT_EVENT.ZIP_FILE_ID,
OASIS.EVT_EVENT.LEVENTID,
OASIS.EVT_EVENT.IEVENTTYPE,
OASIS.EVT_EVENT.EVENT_TIME,
OASIS.EVT_EVENT.EVENT_TIME_NANOSEC,
OASIS.EVT_EVENT.LASSAYNUMBER,
ECON.ZIP_FILE.CRTDATE,TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'YYYY'),TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'MON'),TO_CHAR(OASIS.EVT_EVENT.EVENT_TIME,'DD');

Concatenate (QVTable)
load
J_NO,
CYCLE_NO,
ZIP_FILE_ID
from E151_QVD.qvd (qvd);

Data Time Processing in the Second pass Load on Resident Data.

Thu, 21 May 2009 03:51:11 GMT

Hi Rakesh ,

As u have suggested , i did write " as " in all the fields , it worked faster . But i do not see the "QVD Optimized Statement".

and the Concatenation (QVTable) Load ..... also been modified.

Any ways thanks for ur suggestion (for good tips on writing the Scripts).

Now i can see faster upload.

Regards

Debashish.