Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try to use a preceding load instead of a resident load to create your final fact table.
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
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.
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
I still don't see why you need a resident load to create your fact table from TEMPFact.
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.]
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?
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.
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