Merging multiple rows into one, but always show the rest of the values when one is chosen

[Kunkulis]

Hello,

I have a data set with some information, this would be some dummy data:

Person	Country
Kunkulis	USA
Kunkulis	Germany
Kunkulis	Spain

From other question I have found, that by just using this formula:

=Aggr(Concat(DISTINCT Country, ', '), Person)

I will get the desired outcome:

Person	Country
Kunkulis	USA, Germany, Spain

But now if I would filter out, to show all the Person that have been to USA, I get this result:

Person	Country
Kunkulis	USA

Where I would like to see this (with additional dummies):

Person	Country
Kunkulis	USA, Germany, Spain
Bob	USA
Rob	Canada, Portugal, Sweden, USA

Any idea how this could be achieved? 

[Marijn]

You can also try to filter on *USA*, then QlikView will search for all values where USA is included.

[Kunkulis]

I have a ListBox with all the countries, so selecting USA from it should produce the last table

[Marijn]

No, it shouldn't. When you search for USA, you search for an exact match. When your search for *USA*, it searches for USA and allows for characters before and after. Because you concatenated the Countries, you need to use the asterisks.

[rwunderlich]

You don't need the Aggr() if you are using Person as a dimension in your chart.   I believe the expression you want is:

Concat({<Country=,Person=P()>}DISTINCT Country, ', ') 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Kunkulis]

Thank you for the expression, but what is P()? 
I think due to this, the expression is failing for me.

[Kunkulis]

That would be so, if this concatenation would happen in the Load, but I am performing it in a chart (straight table).

Thus the List box would still show all the countries separated

[rwunderlich]

p() is a Set Function.

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...

-Rob

[Kunkulis]

Hmm, I keep getting "Error in calculated dimensions" both trying 

Concat({<Country=,Person=P()>}DISTINCT Country, ', ')  

and

Concat({<Country=,Person=P(Person)>}DISTINCT Country, ', ')  

Does it change anything that I am trying to get this value as a dimension in a chart (straight table) ? 

[GeigerS]

Select the range of cells where you want to merge rows.Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.

ACAeronet Sign In