2 Replies Latest reply: Jul 23, 2014 8:09 AM by Mahesh Agrawal RSS

    Aggregating days to month sum

      Hello dear community, i have a strange problem while using group by for the first time, maybe someone can pint me to the mistake i made.

       

      The goal is, to aggregate all daily values which represent amounts that can be added (so, f.e. not temperatures)

      So far, i found out, that aggregate does not work in the script.

      Why would someone implement something, that only works in objects on the gui ??

       

      So, the alternative would be load with the "group by" option, which loads the data that should be aggregated through a resident load.

       

      Qlikview responds with an syntax error to the short load scrip below.

      but if i remove

      "sum(MV_Data) as MW_MonData" and correct the ",", i get the following:

       

      "Aggregation expressions required by GROUP BY clause"

       

       

       

      Values_Aggr_Month:

      noconcatenate LOAD

           ID_SENS,

           ID_VALUE,      // water, heating, electricity etc.

           UNIT,               // m3, kwh, liter

           DATE_month,  // f.e. 201401

           NR_Building,

       

          // MV_day,     // former number of the day, not used in this table

             MV_Data,     // numerical content for ID_VALUE

         

           sum(MV_Data) as MW_MonData // whats wrong whith this one ?

       

      RESIDENT DaiylValues

      group by DATE_month, ID_SENS;

       

      Any suggestions ? Thanks in advance.

        • Re: Aggregating days to month sum
          Joonas Jäspi

          You need to all fields to group by and remove MV_DATA field.

           

          Values_Aggr_Month:

          noconcatenate LOAD

               ID_SENS,

               ID_VALUE,      // water, heating, electricity etc.

               UNIT,               // m3, kwh, liter

               DATE_month,  // f.e. 201401

               NR_Building,

               sum(MV_Data) as MW_MonData // whats wrong whith this one ?

          RESIDENT DaiylValues

          group by DATE_month, ID_SENS,ID_VALUE,UNIT,NR_Building;

          • Re: Aggregating days to month sum
            Mahesh Agrawal

            Whenever we do aggregation using group by, we have to add all the columns, which are to be shown, in group by clause except the column on which  aggregation is being done.

             

             

            Load col1,clo2,clo3,sum(col4)

            Resident

            Tab1

            group by

            col1,col2,col3;