Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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;
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
;
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
Thanks Gysbert and Anand, this worked for me!
I think the problem was with the concatenation of the table?
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];
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,.