topic Re: Merge Multiple Rows Into a single Comma Separated Row in QlikView

Merge Multiple Rows Into a single Comma Separated Row

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

In a straight table, i have the below data:

Name	Number	Date	Cost	Country
ABC Enterprise	1234	13-JUN-18	100	USA
ABC Enterprise	1234	13-JUN-18	100	UK
ABC Enterprise	1234	13-JUN-18	100	FRANCE

If i remove the Country column, then i only get one row.

How can i add the country column and make the row comma separated so that i get only one row? like below:

Name	Number	Date	Cost	Country
ABC Enterprise	1234	13-JUN-18	100	USA,UK,FRANCE

I also need only distinct countries to be displayed.

I tried "CONCAT(COUNTRY,',') , but its not working.

Any help would be appreciated.

Thanks

Taher

Re: Merge Multiple Rows Into a single Comma Separated Row

sunny_talwar — Wed, 13 Jun 2018 21:06:25 GMT

Try this

Aggr(Concat(DISTINCT COUNTRY, ', '), Name, Number, Date, Cost)

Re: Merge Multiple Rows Into a single Comma Separated Row

MarcoWedel — Wed, 13 Jun 2018 21:55:57 GMT

Did you try

CONCAT(COUNTRY,',')

as expression or (calculated) dimension?

Using it as expression with Name, Number, Date, Cost as dimensions should work.

hope this helps

regards

Marco

Re: Merge Multiple Rows Into a single Comma Separated Row

Anonymous — Wed, 13 Jun 2018 21:59:55 GMT

Hi Macro

I tried it as a calculated dimension initially.

But when i tried the condition suggested by Sunny in Calculated dimension, it worked fine.

Thanks

Re: Merge Multiple Rows Into a single Comma Separated Row

neerajthakur — Wed, 02 Feb 2022 06:42:22 GMT

Hi, Sunny I tried this in Qlik Sense it's not working and error is produced at first bracket itself. Please let me know if syntax needs to be different for Qlik Sense