Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Append column values to another column

sonal
6 - Meteoroid

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

 

visitorIdtype (actionDetails 1)type (actionDetails 0)timeSpent (actionDetails 1)timeSpent (actionDetails 0)Type (actionDetails 2)timeSpent (actionDetails 2)
5d857ffa0a37eba6actionaction1787action56
6036633a90239aedactionOrder confirmation1046Order confirmation62
630dc5b99f1bec94eventaction1142action42
68d3938ce63ececaeventaction624Order confirmation2
698576ad80159d7bsearchaction94Order confirmation14
6bc01d2e10a3bf50actionDownload 563Download12
6ee206eebab852e6actioncheckout1127checkout65

 

 

Table 2 result expected :

 

visitorIdTypeTime Spent
5d857ffa0a37eba6action17
6036633a90239aedaction10
630dc5b99f1bec94event11
68d3938ce63ececaevent6
698576ad80159d7bsearch9
6bc01d2e10a3bf50action 
6ee206eebab852e6action11
5d857ffa0a37eba6action87
6036633a90239aedOrder confirmation46
630dc5b99f1bec94action42
68d3938ce63ececaaction24
698576ad80159d7baction4
6bc01d2e10a3bf50Download563
6ee206eebab852e6checkout27
5d857ffa0a37eba6action56
6036633a90239aedOrder confirmation62
630dc5b99f1bec94action42
68d3938ce63ececaOrder confirmation2
698576ad80159d7bOrder confirmation14
6bc01d2e10a3bf50Download12
6ee206eebab852e6checkout65

 

 

2 REPLIES 2
jdunkerley79
ACE Emeritus
ACE Emeritus

You can do this with Transpose and Cross Tab tools.

jdunkerley79_0-1603984641056.png

 

- 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
22 - Nova
22 - Nova

Hi @sonal 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1603987194501.png

 

Workflow:

atcodedog05_1-1603987222704.png

 

Hope this helps 🙂


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

Labels
Top Solution Authors