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

Convert Row to Column

html82
7 - Meteor

Hello Alteryx Expert,

 

How to convert data from below format

 

NameString
websitegoogle.com
nameLarry
websiteapple.com
nameTim
websitemicrosoft.com
nameBill

 

To

 

websitename
google.comLarry
apple.comTim
microsoft.comBill
14 REPLIES 14
Aguisande
15 - Aurora
15 - Aurora

Hi @html82

As almost all operations, there are several ways to accomplish them in Alteryx. What I'd do is to standarize the Records first.

To do this, assign a RecordID, and then use a MultiRow formula tool to assign to the second record, the same RecordID as the previous record (Since I guessed the order matters in the pairing)

workflow.PNG

The MultiRow Config should be like this (Update values for RecordID, and the Expression should be:

 

IF [RecordID]!=1 THEN [RecordID]-[Row-1:RecordID]  
ELSE 1
ENDIF

(If not the first RecordID, assign the previous RecordID's Value), else (Is the First Record, just assign 1)

multirow config.PNG 

 

 

Then, CrossTab, grouping by RecordID (Because of the results of the previous step, they should be the same for every pair of records):

CrossTab.PNG

 

 

You can see the results here:

Output.PNG

 

Hope this helps.

html82
7 - Meteor

Hi, this would work for this scenario, but I looking flexible solution, example below:

 

websitegoogle.com
nameLarry
fromMichigan
websiteapple.com
nameTim
fromAlabama
websitestackabuse.com
nameScott
fromNebraska

 

In this case, the solution would generate wrong output because there a new data 'from' which would break logic.

MarqueeCrew
20 - Arcturus
20 - Arcturus

Good morning @html82!

 

I've extended @Aguisande's solution to dynamically look at your field names and create a denormalized view of the data.  The first step is to find all of the "Named" fields and then to create records for each set.  The assumption being that the set of field names is constant.  If you add a DATE field or Subject field (or both), this solution will provide an answer.

 

Capture.png

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
mborriero
11 - Bolide

Hi @html82,

 

Please find attached my approach. Let me know if you need any clarifications.

mborriero
11 - Bolide

Just posted my solution, it is identical to yours!!!! 

Aguisande
15 - Aurora
15 - Aurora

Hi @html82

Since the variation in the use case came after, it wasn´t included. Looking t @MarqueeCrew 's solution, It'll do perfectly what the second case explained needs.

html82
7 - Meteor

Excellent !! Can you explain how the multi row formula working here ?

html82
7 - Meteor

Excellent ! Thanks for your feedback

MarqueeCrew
20 - Arcturus
20 - Arcturus

@html82,

 

I've grouped all of the "NAMES" into the 3 unique values, "From, Name and Website" and numbered each as 1,2,3.  Then after joining these three values to the incoming 9 records from the original data (where I previously numbered each unique record) I have ValueID 1, RecordID 3,6,9 and Value ID 2, RecordID 2,5,8 and ValueID 3 and RecordID 1,4,7.  What I need to do is renumber the RecordID to be the same value for each set (e.g. 3,6,9 becomes 1 and 2,5,8 becomes 2).

 

The multi-row formula is GROUPED by ValueID.  

The formula that I am applying is:  

[Row-1:RecordID]+1

The configuration for "Values that don't Exist" is set to 0.

 

For the first record in the group (ValueID), this becomes 0 + 1 = 1.  This formula adds one (1) to the prior row value for RecordID. 1,2,3 is the result for each of the sets.  Now we are ready for the Cross Tab function.

 

Capture.png

Capture.png

 

Above is the before and after view of data coming into the Multi-Row Formula tool.

 

Cheers,

Mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels