2 Replies Latest reply: Nov 18, 2011 4:37 PM by John Witherspoon RSS

    How can apply SUM in the script

      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
          Steve Dark

          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
              John Witherspoon

              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.