Concatenate Dynamic Columns

[Ashni Sedani 2]

Hi all,

I'd like for the user of an analysis dashboard to be able to select a series of columns using a multiple-select list box, and for a calculated column to concatenate the values in the selected columns. I can do the first part in terms of setting up the document property, but am having trouble then using a string list to define the series of columns that need concatenating..

E.g. In a text area, the user would select a series of columns of the data table below, which gets stored as a String List property called 'column_list'.

If the user selects Column_1, Column_3 and Column_4, ${column_list} = 'Column_1,Column_3,Column_4'

 

 

 

Column_1

Column_2

Column_3

Column_4

 

 

Val_1

Val_2

Val_3

Val_4

 

 

 

I would then like a new column, [concat_values] to be calculated by concatenating the values in the selected columns, separated by ",".

In this case, the result would be 'Val_1,Val_3,Val_4'.

 

Is anybody able to advise on how to do this Or if there is an easier way

Thanks

[Gaia Paolini]

I think this will work:

Concatenate($map("[${selbox}]", ",', ',"))

where ${selbox} is your string list property.

The $map uses a separator",', '," containing an extra comma,that fools Concatenate into thinking it is an extra string to concatenate, otherwise you don't get the comma between Val_1, Val_2 but Val_1Val_2 instead. You can adjust the space after the comma.

Gaia

[Ashni Sedani 2]

Works brilliantly, thank you! Only one exception - when I have only one column selected for concatenation, which may well be the case, then all values in that column (across all rows) are concatenated. Any ideas of how to get around this

Thanks!

[Gaia Paolini]

try adding a null at the end:

 

Concatenate($map("[${selbox}]", ",', ',"),null)

[Ashni Sedani 2]

great, thanks so much! all sorted!