Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table where I need to concat values of a column to reduce duplicate rows
Input output
ID | Loc | Floor | ID | Loc | Floor | ||
A123 | A1 | 1 | A123 | A1 | 1 | ||
A124 | B1 | 2 | A123 | A2 | 2,1 | ||
A127 | C1 | 4 | A123 | A3 | 1,2 | ||
A123 | A2 | 2 | A124 | B1 | 2,1 | ||
A123 | A3 | 1 | A124 | B2 | 2 | ||
A123 | A2 | 1 | A127 | C1 | 4 | ||
A124 | B1 | 2 | A127 | C2 | 4,2 | ||
A124 | B2 | 2 | |||||
A127 | C2 | 4 | |||||
A123 | A3 | 2 | |||||
A124 | B1 | 1 | |||||
A127 | C2 | 2 |
Any help on this. This I want to do it in dimension level . On UI
Within an expression with: concat(Floor, ', ') and in a dimension with: aggr(concat(Floor, ', '), ID, Loc)
- Marcus
Try this
Temp:
LOAD ID,
Loc,
Floor
FROM [Input Table];
NoConcatenate
LOAD ID,
Loc,
Concat(Floor,',') as Floor
Resident Temp
Group By ID,Loc
Order By ID;
DROP Table Temp;
Thanks Peter. However in sheet level in dimension or in expression, how can I add the shortcut formula to achieve this.
Within an expression with: concat(Floor, ', ') and in a dimension with: aggr(concat(Floor, ', '), ID, Loc)
- Marcus