topic How can apply SUM in the script in QlikView

How can apply SUM in the script

Fri, 18 Nov 2011 15:01:09 GMT

Hello,

Is possible to have the SUM function in the script , if so can you please give an example , I want to sum a numeric fiield for the records which have the same value in 3 alpha fileds.

Thanks and regards,

Fernando.

How can apply SUM in the script

stevedark — Fri, 18 Nov 2011 16:29:45 GMT

Hi there - typically it is better not to sum in the load script, as QlikView can make light works of aggregating many many rows at display time. If you do wish to aggregate in the load it will be something like this:

LOAD

Line,

Action,

sum(Value1) as Value1,

sum(Value2) as Value2,

sum(Value3) as Value3

FROM [Spreadsheet.xls]

(biff, embedded labels, header is 4 lines, table is Sheet1$)

GROUP BY Line, Action;

Note it is the GROUP BY at the end that is doing the grouping, and as you assume in your post the SUM that does the aggregation.

The syntax is slightly different with different data sources - if you have problems post back with details of where your data is coming from.

Hope this helps.

Steve

How can apply SUM in the script

johnw — Fri, 18 Nov 2011 21:37:43 GMT

Steve Dark wrote:
...typically it is better not to sum in the load script, as QlikView can make light works of aggregating many many rows at display time...

Quoted for emphasis. It MAY make sense to aggregate during the script, but it usually does not. There is usually a better solution.