topic Re: Aggregating daily data by month in load script in QlikView

Aggregating daily data by month in load script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have Orders table which has daily orders. I would like to create a summary table which aggregates the data by month. Storing daily transactions is making my database huge and takes long time to process. I don't need to store the values by day.

Tried the below code, but its not working.

OrdersSummaryTbl:

Load Transaction_Date,

MonthStart(Transaction_Date) as MonthStart,

Country,

Product_Name,

Sum(Order_Count) as Orders,

Sum(Order_Amount) as OrderAmt

Resident Orders

Group by Transaction_Date,

Country,

Product_Name

MonthStart;

I just need to aggregate the data by month and store it. Please can you share your thoughts.

Re: Aggregating daily data by month in load script

aarkay29 — Thu, 23 Feb 2017 23:18:16 GMT

OrdersSummaryTbl:

Load

Country,

Product_Name,

Month

Sum(Order_Count) as Orders,

Sum(Order_Amount) as OrderAmt

Group by

Country,

Product_Name,

Month;

Load

Transaction_Date,

Month(Transaction_Date) as Month,

Country,

Product_Name,

Order_Count

Order_Amount

Resident

Orders;

Re: Aggregating daily data by month in load script

maxgro — Thu, 23 Feb 2017 23:22:15 GMT

try this

OrdersSummaryTbl:

Load

//Transaction_Date,

MonthStart(Transaction_Date) as MonthStart,

Country,

Product_Name,

Sum(Order_Count) as Orders,

Sum(Order_Amount) as OrderAmt

Resident

Orders

Group by

MonthStart(Transaction_Date),

Country,

Product_Name

//MonthStart

;

Re: Aggregating daily data by month in load script

piotrtk — Thu, 23 Feb 2017 23:27:38 GMT

Hi,

Try this:

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

OrdersSummaryTemp:

Load

Year(Transaction_Date) & '-' & Month(Transaction_Date) as KeyDate,

Country,

Product_Name,

Order_Count,

Order_Amount

Resident Orders;

OrdersSummaryTbl:

KeyDate

Country,

Product_Name,

Sum(Order_Count) as Orders,

Sum(Order_Amount) as OrderAmt,

Resident OrdersSummaryTemp

Group by KeyDate, Country, Product_Name;

DROP TABLE OrdersSummaryTemp;

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

Regards,

Piotr

Re: Aggregating daily data by month in load script

Anonymous — Fri, 24 Feb 2017 11:16:57 GMT

Thank you all. It worked.