2 Replies Latest reply on Sep 24, 2013 9:57 AM by piero giansanti

# Is it possible to create a field where instead of a mathematical operation (average, median, stdev, sum....) all the 'values' are concatenated, using a special character (i.e. ";") between them?

Hi all,

I am currently working with a data set like this

 UID0001 a 10 UID0002 a 20 UID0003 b 5 UID0004 a 30 UID0005 c 5 UID0006 d 5

What I would like to do is to sum values in column 3 on the basis of the values in column 2 (a,b,c and d).

 a 60 b 5 c 5 d 5

This is easy, ok! However, at the same time, I would like to concatenate the values in column 1, still on the basis of the values in column 2,to obtain this

 a 60 UID0001;UID0002;UID0004 b 5 UID0003 c 5 UID0005 d 5 UID0006

How can I do this?

• ###### 1. Re: Is it possible to create a field where instead of a mathematical operation (average, median, stdev, sum....) all the 'values' are concatenated, using a special character (i.e. ";") between them?

Hi Piero,

You can accomplish this by making use of the PREVIOUS_VALUE function in Tableau. This function is a table calc so you need to make sure you set partitioning and addressing up properly, but I can get the desired result with this and a filter to make sure only the last iteration of the concatenation is showing.

Please see the attached workbook for an example.

All the best,

Robin.

• ###### 2. Re: Is it possible to create a field where instead of a mathematical operation (average, median, stdev, sum....) all the 'values' are concatenated, using a special character (i.e. ";") between them?

Hi Robin,

Thank you for your help, indeed it works perfectly!

However I have a small problem now, I am trying to export the data as crosstab.

My dataset is quite big and after all the aggregation/calculation I ended up with more than 20K rows and 1 column. Although I am using a 64-bit PC with 64GB of RAM, If I try to export or copy as crosstab, Tableau goes out of memory (More memory needed to generate the view. Please reduce the level of detail in the view or close some applications.)

I think this is due to the 'complex concatenation' calculation it has to do (if I remove it I can export the data with no problem), so, is there any other way to export the aggregated data as I see them in the worksheet?