Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggregating daily data by month in load script

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP


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

;

View solution in original post

4 Replies
aarkay29
Specialist
Specialist

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;

maxgro
MVP
MVP


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

;

piotrtk
Contributor II
Contributor II

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

Anonymous
Not applicable
Author

Thank you all. It worked.