1 Reply Latest reply: Oct 21, 2016 11:39 AM by Sunny Talwar RSS

    Problem with 'group by'

    Ciro Gomez

      I am trying to group by location and month year in this script.

       

      Load

      *

      ,ApplyMap( 'LOCNameMap', LOC_ID, '*Unknown' ) as Location;

      LOAD

      HSP_ACCOUNT_ID ,

      DEPARTMENT_ID,

          QualityDate,

      date(MonthStart(QualityDate),'MMM YYYY') as MonthYear,

         QualityDateTime,

          MetricID,

          Numerator,

          Denominator,

          ApplyMap( 'LOCIDMap', DEPARTMENT_ID, '*Unknown' ) as LOC_ID

       

      I know in qlikview group by you have to list all of the fields you are not aggregating, and none of these fields have anything to aggregate,

       

       

      I tried to inner join the above table to

       

       

      max(HSP_Account_ID)

      MonthYear

      Location

      group by

      MonthYear

      Location

      resident original table.

       

      Is this the best way to do this?

       

      Thank you.

        • Re: Problem with 'group by'
          Sunny Talwar

          I think you havn't put the complete script down, but from what I understand, Inner Join or Left Join or a regular Join would be the best thing if you want to combine them. You can also keep it as a separate table to avoid creating duplication of the aggregated field.