topic Concatenate aggregated field in App Development

Concatenate aggregated field

ruben_moya — Sat, 16 Nov 2024 02:16:40 GMT

Dear Experts,

having de following data:

Group	Company	Consumption
1	A	1000
1	B	2000
1	C	1000
2	D	1000
2	E	3000
2	F	4000

Group 1	4000
Group 2	8000

I need to concatenate the field Company based on a condition in the Consumption of the GROUP they belong. I've been able to get all the Companies concatenated when I aggregate them by Company:

CONCAT(IF(AGGR(sum(Consumption),Company)> 2000,Company),'|')

E|F

But when I try to get the Companies based on a Group agregation it doesn't work. I would expect D|E|F

CONCAT(IF(AGGR(sum(Consumption),Group)> 5000 ,Company),'|')

Could someone explain me why this is happening?

Thanks in advance

Re: Concatenate aggregated field

SerhanKaraer — Fri, 05 Jun 2020 14:31:16 GMT

Hi Ruben,

You have to use NODISTINCT parameter in Aggr() function.

CONCAT(IF(AGGR(NODISTINCT sum(Consumption),Group)> 5000 ,Company),'|')

Re: Concatenate aggregated field

ruben_moya — Mon, 08 Jun 2020 10:46:50 GMT

That worked perfectñy, thanks a lot.

Just curious, what is the logic behind the need of needing nodistinct?

Thanks again!

Re: Concatenate aggregated field

SerhanKaraer — Mon, 08 Jun 2020 11:07:05 GMT

Aggr's temp table does not fit your current table Ruben.

Here is an explanation with data:

https://community.qlik.com/t5/QlikView-App-Development/What-NODISTINCT-parameter-does-in-AGGR-function/m-p/368156