4 Replies Latest reply: Mar 16, 2016 6:02 AM by Gysbert Wassenaar RSS

    Group by with aggregated field

    Jim van der Meel

      Dear community,

       

      I have started using Qlik Sense this week. So far I have done the beginners and next steps in scripting tutorials. However, I still can't figure out the best way to aggregate my data. Hope you can help!

       

      The data looks like this:

      job_idunique nameconversionssales
      1234AAA1

      2

      1234AAA42
      2222BBB51
      2226BBB61
      3333CCC12

       

      I would like to get to this:

      Firstvalue(job_id)unique namesum(conversions)sum(sales)
      1234AAA54
      2222BBB112
      3333CCC12

       

      So basically I would first like to do a group by with job_id (which works within the load statement).

      After this I still have several duplicate unique names and would like to aggregate further by unique name.

       

      I tried to do this by creating a temp table with the first aggregation on job_id and loading that table into a new one with the group by on unique name. I get an invalid expression.

       

      tmpData:

      LOAD

          job_id,

          sum(conversions) as conversions,

          sum(sales) as sales,

          FirstValue(uniquename) as uniquename,

      FROM [........]

      (txt, codepage is 1252, embedded labels, delimiter is ';', msq)

      GROUP BY job_id;

       

      finaldata:

      LOAD

          FirstValue(job_id),

          uniquename,

          conversions,

          sales

      Resident tmpData;

      group by uniquename;