Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need More Efficient "Group By" Load

Hi Everybody,

I'm trying to find a more efficient way to create a table that sums and groups records by a certain key. I have the following data that I want to load:

Order_NumberProductDateOrder_QuantityQuantity_Filled
100001Product_106/01/20122920
100001Product_206/01/20121313
100001Product_306/01/2012143
100002Product_107/10/20121111
100002Product_207/10/20121111
100002Product_307/10/20121717
100003Product_107/12/20122825
100003Product_307/12/2012180

I will only need to look at this data by Product and Month. I don't need it broken down by Order_Number or a specific date.

The only way I know how to do this is to create separate tables, make a Product_Date_Key, create another table to sum the data and group by the key, add and drop tables, etc. This works fine but I'd like to have it be more efficient if possible.

The actual data that I'm using is thousands of products on thousands of orders for the last two years.

Tags (3)
1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Need More Efficient "Group By" Load

The following scripts will work:

Initial_Load:

load *, MonthName(Date) as Month;

LOAD * INLINE [

    Order_Number, Product, Date, Order_Quantity, Quantity_Filled

    100001, Product_1, 41061, 29, 20

    100001, Product_2, 41061, 13, 13

    100001, Product_3, 41061, 14, 3

    100002, Product_1, 41100, 11, 11

    100002, Product_2, 41100, 11, 11

    100002, Product_3, 41100, 17, 17

    100003, Product_1, 41102, 28, 25

    100003, Product_3, 41102, 18, 0

];

Data:

LOAD Month, Product,Sum(Order_Quantity) as OrdQty, Sum(Quantity_Filled) as FilledQty Resident Initial_Load Group By          Month, Product;

Drop Table Initial_Load;

4 Replies
nagaiank
Valued Contributor III

Re: Need More Efficient "Group By" Load

The following scripts will work:

Initial_Load:

load *, MonthName(Date) as Month;

LOAD * INLINE [

    Order_Number, Product, Date, Order_Quantity, Quantity_Filled

    100001, Product_1, 41061, 29, 20

    100001, Product_2, 41061, 13, 13

    100001, Product_3, 41061, 14, 3

    100002, Product_1, 41100, 11, 11

    100002, Product_2, 41100, 11, 11

    100002, Product_3, 41100, 17, 17

    100003, Product_1, 41102, 28, 25

    100003, Product_3, 41102, 18, 0

];

Data:

LOAD Month, Product,Sum(Order_Quantity) as OrdQty, Sum(Quantity_Filled) as FilledQty Resident Initial_Load Group By          Month, Product;

Drop Table Initial_Load;

Not applicable

Re: Need More Efficient "Group By" Load

Perfect! Thanks!

Not applicable

Re: Need More Efficient "Group By" Load

Hello

I would do any aggregations on the load script, and I would let QV do them for you.
I uploaded the same file and got to the same results without changing the load script, I think it will work faster and will give you more flexibility in the future
I attach the qvw example
Hope this helps
Regards,

Not applicable

Re: Need More Efficient "Group By" Load

Thanks.

That's how I was doing it originally but the problem was that I was loading too much data that I didn't need. I only ever need to know the total quantity for a product by month. I never used quantities by order number or by what day of the month so this was slowing the file down as I was loading thousands of different products across thousands of orders.

Community Browser