Concat distinct qlik expression

[Jsobrinho77]

H guys,

I have a dimension that contains values, and I need to show the distinct values by hierarchy dimension, in this example, I'm using only one level, Category1.

I'm trying to use: concat(distinct Dimension, '---- ') but the result is:

AB568----AB568;XOI564;IOP59;LKI442;..

But the expected result is:  

AB568;XOI564;IOP59;LKI442

This is possible?

Dimension Category, Dimension:

Category1, AB568

Category1, AB568;XOI564;IOP59;LKI442;..

[Andrei_Cusnir · Accepted solution]

Hello,

There might be other ways to achieve this use case scenario, however this is one of them:

1. You will have to generate the data in a separate table within Data load editor

2. This will load the data that you specified:

Dataset:
Load * inline [
    Dimension Category, Dimension
    Category1, AB568
    Category1, AB568;XOI564;IOP59;LKI442;
];

3. This will separate the values from the entire field and will put them in a new table:

TempData:
Load
    SubField(Dimension, ';') as TempDimension
Resident Dataset;

4. Finally this will remove any empty generated cells and will drop the temp table:

NoConcatenate
FinalData:
Load 
    TempDimension as TempDimension
    Resident TempData
Where Not Match(TempDimension, '');

Drop Table TempData;

5. After that you can just use the expression: =Concat(DISTINCT TempDimension, ';') and the outcome is:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue or address all your concerns, please mark it as accepted solution to give further visibility to other community members.