Group by with aggregated field

[#1]

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_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

I would like to get to this:

Firstvalue(job_id)	unique name	sum(conversions)	sum(sales)
1234	AAA	5	4
2222	BBB	11	2
3333	CCC	1	2

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;

[Kushal_Chawda]

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;

[Gysbert_Wassenaar]

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

     ;

[its_anandrjs · Accepted solution]

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

[#5]

Thanks Gysbert and Anand, this worked for me!

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

[sunny_talwar]

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];

[Gysbert_Wassenaar]

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,.