Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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