Append column values to another column

[sonal]

Hi, I need to append column values. The column names are similar but go like type ( actiondetails1 ) , type (actiondetails2) ... type (action details 40) .. similarly for time spent (action details 1), time spent (action details 2).. time spent (action details 40)... I need to combine the data from the type column and the time spent column. Any ideas on how to achieve this?

Table 1: Existing

visitorId	type (actionDetails 1)	type (actionDetails 0)	timeSpent (actionDetails 1)	timeSpent (actionDetails 0)	Type (actionDetails 2)	timeSpent (actionDetails 2)
5d857ffa0a37eba6	action	action	17	87	action	56
6036633a90239aed	action	Order confirmation	10	46	Order confirmation	62
630dc5b99f1bec94	event	action	11	42	action	42
68d3938ce63ececa	event	action	6	24	Order confirmation	2
698576ad80159d7b	search	action	9	4	Order confirmation	14
6bc01d2e10a3bf50	action	Download		563	Download	12
6ee206eebab852e6	action	checkout	11	27	checkout	65

Table 2 result expected :

visitorId	Type	Time Spent
5d857ffa0a37eba6	action	17
6036633a90239aed	action	10
630dc5b99f1bec94	event	11
68d3938ce63ececa	event	6
698576ad80159d7b	search	9
6bc01d2e10a3bf50	action
6ee206eebab852e6	action	11
5d857ffa0a37eba6	action	87
6036633a90239aed	Order confirmation	46
630dc5b99f1bec94	action	42
68d3938ce63ececa	action	24
698576ad80159d7b	action	4
6bc01d2e10a3bf50	Download	563
6ee206eebab852e6	checkout	27
5d857ffa0a37eba6	action	56
6036633a90239aed	Order confirmation	62
630dc5b99f1bec94	action	42
68d3938ce63ececa	Order confirmation	2
698576ad80159d7b	Order confirmation	14
6bc01d2e10a3bf50	Download	12
6ee206eebab852e6	checkout	65

[jdunkerley79 · Accepted solution]

You can do this with Transpose and Cross Tab tools.

- First Transpose so row bases

- Then using a RegEx tool to parse to Type and Id (0,1,2)

- Next Cross Tab to make the rows grouped by visitorID and Id

- Finally, tidy up select tool

Quick sample attached

[atcodedog05]

Hi @sonal 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍