Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Group by with aggregated field

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;

1 Solution

Accepted Solutions

Re: Group by with aggregated field

Your code is correct with some changes try this code

Data:

LOAD * INLINE [

    job_id, unique name, conversions, sales

    1234, AAA, 1, 2

    1234, AAA, 4, 2

    2222, BBB, 5, 1

    2226, BBB, 6, 1

    3333, CCC, 1, 2

];

NoConcatenate

Final:

LOAD

[unique name],

FirstValue( job_id) as  job_id,

Sum( conversions) as conversions,

Sum( sales) as sales

Resident Data

Group By

[unique name];

DROP Table Data;

Regards

Anand

6 Replies

Re: Group by with aggregated field

try,

LOAD

    job_id,

    sum(conversions) as conversions,

    sum(sales) as sales,

    concat( distinct uniquename,'') as uniquename,

FROM [........]

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

GROUP BY job_id;

or

tmpData:

LOAD job_id,

          conversions,

          sales

          Subfield(uniquename,'-',1) as uniquename;

LOAD

    job_id,

    sum(conversions) as conversions,

    sum(sales) as sales,

    concat(uniquename,'-') as uniquename,

FROM [........]

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

GROUP BY job_id;

Re: Group by with aggregated field

The error you get is because you didn't list all the fields that you don't use in an aggregate function in the group by clause.

finaldata:

NOCONCATENATE

LOAD

    FirstValue(job_id) as job_id,

    uniquename,

    conversions,

    sales

RESIDENT

     tmpData

GROUP BY

     uniquename,

    conversions,

    sales

     ;


talk is cheap, supply exceeds demand

Re: Group by with aggregated field

Your code is correct with some changes try this code

Data:

LOAD * INLINE [

    job_id, unique name, conversions, sales

    1234, AAA, 1, 2

    1234, AAA, 4, 2

    2222, BBB, 5, 1

    2226, BBB, 6, 1

    3333, CCC, 1, 2

];

NoConcatenate

Final:

LOAD

[unique name],

FirstValue( job_id) as  job_id,

Sum( conversions) as conversions,

Sum( sales) as sales

Resident Data

Group By

[unique name];

DROP Table Data;

Regards

Anand

Not applicable

Re: Group by with aggregated field

Thanks Gysbert and Anand, this worked for me!

I think the problem was with the concatenation of the table?

Re: Group by with aggregated field

May be this:

Table:

LOAD FirstValue(job_id) as job_id,

    [unique name],

    Sum(conversions) as conversions,

    Sum(sales) as sales

FROM

[https://community.qlik.com/thread/209680]

(html, codepage is 1252, embedded labels, table is @1)

Group By [unique name];


Capture.PNG

Re: Group by with aggregated field

No, that wouldn't cause the error. We used noconcatenate because we also renamed FirstValue(job_id) to job_id. That makes the table Final have exactly the same fields as the table tmpData. To avoid the records ending up in tmpData because of that the noconcatenate directive is necessary,.


talk is cheap, supply exceeds demand
Community Browser