topic Re: How to use aggregation in the script level - Backend in QlikView

How to use aggregation in the script level - Backend

Tue, 20 Jan 2015 11:43:10 GMT

I need aggregate the value with respect to each invoice , i have the values in terms of row level data. Kindly suggest how can i implement it in the script part

Thanks

Sathis

Re: How to use aggregation in the script level - Backend

Peter_Cammaert — Tue, 20 Jan 2015 11:49:06 GMT

The GROUP BY clause is your friend here. It may take you some time to figure out exactly how it works, though. An example may help. Assuming you have Orders and OrderLines tables and you want to add the Order Amount to your Orders table:

LEFT JOIN (Orders)

LOAD OrderID, // This will be the JOIN field

Sum(OrderLineAmount) // Sum the Amounts for all OrderLines of every Order

RESIDENT OrderLines

GROUP BY OrderID;

Re: How to use aggregation in the script level - Backend

Anonymous — Tue, 20 Jan 2015 11:50:28 GMT

you need to use group by

select invoice,

sum(value)

from dbtable

group by invoice

Re: How to use aggregation in the script level - Backend

Tue, 20 Jan 2015 14:03:55 GMT

i understood how to use the group by Clause

Thank You

New Group by Clause Leaners :

with the above example

LEFT JOIN (Orders)

LOAD OrderID, // This will be the JOIN field

Customer_Name, // Either Comment or include it in the group by clause

Sum(OrderLineAmount) // Sum the Amounts for all OrderLines of every Order

RESIDENT OrderLines

GROUP BY OrderID;

If in case a ROW ID column is available in the data pls dont include it in the Group by clause because Row ID is a Distinct Column which does not support Group by Clause.

Thanks

Re: How to use aggregation in the script level - Backend

Peter_Cammaert — Tue, 20 Jan 2015 16:09:49 GMT

If it works for you, please mark a discussion answer as Correct. The blue balloon next to your question in the community overview will turn green. That way, other community visitors that are searching for answers will more easily find and visit your discussion. Thanks.