394169_en-US

liliconcom

How do I transpose multiple delimited columns

Hi,

I'm trying to split and transpose multiple pipe delimited columns and would appreciate some help in finding the most efficient way of doing this.

The table contains measurement values at X,Y location for a measurement type.

The number of measurements and X,Y locations can vary for each batch and date.

 

Example data and required output shown below.

 

Start table

Batch	Date	Type	X	Y	Value
A1	01/06/2021	L	0 | 0 | 1 | 1 | 0	0 | 1 | 0 | 1 | 2	0.2 | 0.32 | 0.22 | 0.30 | 0.27
A1	01/06/2021	M	0 | -3 | 0 | 3 | 0	3 | 0 | 0 | 0 | -3	1.743 | 1.732 | 1.741 | 1.740 | 1.7
A2	01/06/2021	L	0 | -3 | 0 | 3	3 | 0 | 0 | 0	0.20 | 0.31 | 0.22 | 0.28
A2	02/06/2021	L	0 | -3 | 0 | 3 | 0	3 | 0 | 0 | 0 | -3	0.20 | 0.31 | 0.23 | 0.28 | 0.23

 

 

Output

Batch	Date	X	Y	L	M
A1	01/06/2021	0	0	0.2	1.741
A1	01/06/2021	0	1	0.32
A1	01/06/2021	1	0	0.22
A1	01/06/2021	1	1	0.3
A1	01/06/2021	0	2	0.27
A1	01/06/2021	0	3		1.743
A1	01/06/2021	-3	0		1.732
A1	01/06/2021	3	0		1.74
A1	01/06/2021	0	-3		1.7
A2	01/06/2021	0	3	0.2
A2	01/06/2021	-3	0	0.31
A2	01/06/2021	0	0	0.22
A2	01/06/2021	3	0	0.28
A2	02/06/2021	0	3	0.2
A2	02/06/2021	-3	0	0.31
A2	02/06/2021	0	0	0.23
A2	02/06/2021	3	0	0.28
A2	02/06/2021	0	-3	0.23

 

Thanks

Data Blending and CleanupRe: How do I transpose multiple delimited columns

Hi txnelson, Thanks for taking the time to answer this.

Unfortunatley the output you get isn't what I'm looking for, but your use of the multiple series stack did start me off in the right direction.

I realised that I need to change the order of the columns in the multiple series stack and then do a split on the data3 column.

gives

then split on data 3

gives required output:

 

Re: How do I transpose multiple delimited columns

Your request is an easily solvable task

To turn the concatenated data into separate columns,

     Select the 3 Columns

     Go to

          Cols=>Utilities=>Text to Columns

           Enter the "|" as the delimiter column

           Click on OK

To transpose the columns go to (this is actually a stacking of the columns)

     Tables=>Stack

     Set up the dialog box like below

Click OK and you will get