Convert Row to Column

[html82]

Hello Alteryx Expert,

How to convert data from below format

Name	String
website	google.com
name	Larry
website	apple.com
name	Tim
website	microsoft.com
name	Bill

To

website	name
google.com	Larry
apple.com	Tim
microsoft.com	Bill

[Aguisande]

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)

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)

 

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

You can see the results here:

Hope this helps.

[html82]

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

website	google.com
name	Larry
from	Michigan
website	apple.com
name	Tim
from	Alabama
website	stackabuse.com
name	Scott
from	Nebraska

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

[MarqueeCrew · Accepted solution]

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.

Cheers,

Mark

[mborriero]

Hi @html82,

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

[mborriero]

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

[Aguisande]

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]

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

[html82]

Excellent ! Thanks for your feedback

[MarqueeCrew]

@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.

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

Cheers,

Mark