topic aggregate fact table in QlikView

aggregate fact table

Anonymous — Mon, 21 Dec 2015 10:16:33 GMT

I have a very huge big fact table , about 1 billions lines .

How could I aggragate the previous years and not aggregate this year ?

Vente:

LOAD

PrixUnitaireArticle,

PrixArticleHT,

PrixRevientTTC,

MargeHT,

MargeTTC,

ValeurRevientHT,

ValeurRevientTTC,

ValeurMargeHT,

ValeurMargeTTC,

PrixVenteHT,

ID,

N°tickets,

EAN,

[Code Caisse],

[Code Site],

[Prix de vente TTC],

Quantité,

[Date Time],

DateOfHire,

Promo,

[Code Article],

[Code Structure],

Alimentaire,

[Prix de Revient],

TVA,

DivKey,

Date ,

DateTest ,

nbrClient,

KeyVenteSite,

code4 ,

Comp,

datecol,

QuantiteCasse,

QuantiteAjust,

oc1,

[PrixPermanentTTC],

[PrixPermanentHT],

Valeur

FROM

E:\QVD\QVD\Vente.qvd

(qvd);

Re: aggregate fact table

maleksafa — Mon, 21 Dec 2015 10:20:31 GMT

depending on the level of details that you want from previous year.

you can do the very basic step, by first loading the data aggregated using sum and group by, with a 'Where' condition to take the previous year (using today() function). and then load the rest of the data in normal mode.

on a side note, if you are loading a big fact table, consider removing unused fields as it will save a lot of space and enhance the performance.

Re: aggregate fact table

marcus_sommer — Mon, 21 Dec 2015 10:32:39 GMT

I suggest you makes two loadings - one is the load of this table with a where-clause on the current year and the second would be an aggregation load over all needed previous years but with only a few fields like an ID and a Year and sum(value) the aggregation loads with so many fields and this amount of data will be very expensive.

- Marcus