Application taking more time!!!

[#1]

Hi All

Can any one guide me on the following issues, I have had a Qvw file with size 500 MB. I am taking it in to the other qvw file as resident ,

and I am transforming the fact table here,

like

Fact:

LOAD

ACCOUNTID,

AutoNumber(DEPTID&'|'&DOCNUM&'|'&DOCTYPE&'|'&DCDTNUM&'|'&DATE&'|'&ReportID&'|'&FIRMTYPE&'|'&DFGH) AS KEY1,

//ACCOUNTID&'|'&DOCNUM&'|'&DOCTYPE&'|'&DCDTNUM&'|'&DATE&'|'&ReportID&'|'&FIRMTYPE&'|'&DFGH AS Account_Key,

AutoNumber(DOCNUM&'|'&DOCTYPE&'|'&DCDTNUM&'|'&DATE&'|'&ReportID&'|'&FIRMTYPE&'|'&DFGH) AS Key2,

num(trim(DEPTID)) AS DEPTID,

num(trim(GLMCU)) AS GLMCU,

num(trim([BRANCH NUMBER])) AS [BRANCH NUMBER],

num('20'&(FISCALYEAR+1),'##0.0') AS FISCALYEAR,

([SALES]/100) AS [SALES],

Period AS Period,

AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1 AS [FiscalDate] ,

AutoNumber(num('20'&(FISCALYEAR+1),'##0.0')&Period) AS Key,

Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY') AS JulianDate,

Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY') AS DATE,

num(Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY')) AS DateNum,

DOCTYPE AS [DOC NUMBER],

DESCRIPTION,

[ABAP DESCRIPTION],

FIRMTYPE AS [Account Type],

FIRMTYPE,

BRANCH,

DOCTYPE,

DOCNUM,

num(DCDTNUM) AS DCDTNUM ,

DATE,

ReportID,

DFGH 

Resident TEMPFact;

and I am calculating the  other aggregation table with

Sum(SALES) AS AGGR_SALES with KEY1,FISCALYEAR,PERIOD etc.

WHEN I run this application I am facing problem with reload time. App taking more time 3 hours how can I improve the reload time here.

Regards

John

[Gysbert_Wassenaar]

Try to use a preceding load instead of a resident load to create your final fact table.

[#3]

Thanks Gysbert Wassenaar.

Here I would like to make a separate fact table with aggregated information on specific columns. I didn't think so it is possible with Preceding load. please excuse me if I am wrong in this and let me know the correct method to work around

Regards

John

[Gysbert_Wassenaar]

I don't see a Group By clause in the load statement for your fact table, so you don't seem to be calculating any aggregations.

[#5]

Yes I have not added the script yet. just I stated those words above sorry for the misleading, the group by clause script is,

AGGR:

KEY1,

Sum([Net Posting]) AS [Net Posting]

Resident TempF099

Group By

FISCAL_YEAR,

Period,

[BRANCH NUMBER],

DEPTID,

GLMCU,

DateNum,

KEY1,

;

Regards

John

[Gysbert_Wassenaar]

I still don't see why you need a resident load to create your fact table from TEMPFact.

[#7]

Because TEMPFact table is coming from a binary load,[fact.qvw application is having this fact table I am loading that application into new application and I am performing the transformations according to my requirements.]

[maxgro]

3 hours for a 500 mb .qvw?

did you drop the fact table after the group by resident load?

or could you post the script and the .log?

[Gysbert_Wassenaar]

Ok, try storing that TEMPFact table into a qvd file directly after the binary load. Then drop that table and use the newly created qvd file to create the final fact table and for the aggregated table. You might want to create that qvd with another qlikview document so you don't need to binary load all the other data from that first qvw file too.

[#10]

Hi Massimo Grossi,

Yes it is taking 3 hours, could you please let me know  how much time an optimized application will take time to reload

on 8 GB RAM personal edition system. My Application has 20 Million records.

Regards

John