4 Replies Latest reply on Oct 9, 2018 12:02 AM by seraj alam

# Split and combine the data in one column

Hello Team,

I have got a requirement regarding the data in different columns and to split the data and add to the single column like below sample data.

 Date Recordings Values 5/30,7/5,3/15 56,45,76 M1,M2,M3 4/13,9/255.17,7/25,6/30,10/25 67,8956,78,98,23 M1, M2M1,M2,M3

Now I need to combine the data like below

New values

5/30,56,M1

7/5,45,M2

3/15,76,M3

It could be the great help for me.

• ###### 1. Re: Split and combine the data in one column

Hi Sagar,

I have sample data as you mentioned and done the calculation like below. Also, I have attached the workbook for your reference.

• ###### 2. Re: Split and combine the data in one column

Hi seeraj,

Thank you for your quick response, could you please send the workbook once again while opening the workbook showing an error.

• ###### 3. Re: Split and combine the data in one column

Hi,

Find my approach below,

Fist you need to split all the fields like this,

Repeat the above steps for other fields (Recordings, Values)

Once done you'll have this view

Now create few calculated field to concatenate split fields from (Date,Recordings,Values)

Split 1: [Date - Split 1]+','+[Recordings - Split 1]+','+[Values - Split 1]

split 2: [Date - Split 2]+','+[Recordings - Split 2]+','+[Values - Split 2]

Split 3: [Date - Split 3]+','+[Recordings - Split 3]+','+[Values - Split 3]

Now bring this source to excel and delete all the fields except calculated Split 1, 2, and 3 which you've created.

Once done then connect the remaining data in tableau and pivot

after pivot you'll have something like this. Go to sheet and use the pivot field values in you canvas and check

Note: If this is one time activity then you can follow this approach else I'd recommend don't do this data cleaning and reshaping manually use any ETL tool for this.

Mahfooj

• ###### 4. Re: Split and combine the data in one column

Here you go..!!