Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live! And don't forget to check out our Digital Costume Thread to get yourself in the mood for a candy binge!

Converting Form Data from Row To Column

Highlighted
5 - Atom

Hi,

How do i convert data in the following format 

 

(unnamed)(unnamed)(unnamed)(unnamed)
(blank)(blank)(blank)ACME Company
Amount 

$500

$600$700
Spread1.63%1.83%2%
Deferment1.2%1.3%1.4%

 

To

 

ID

Amount

SpreadDeferment
ACME Company

$500

1.63%1.2%
ACME Company

$600

1.83%1.3%
ACME Company$7002%1.4%

 

Highlighted
Alteryx Certified Partner

Hi @aCha1120 

 

Solution is attached.

 

By filtering for the name column and then appending it back to the transformed data, it's possible to convert the data.

 

However, if you have multiple IDs within your dataset then you can flag the 3 rows for each ID (Amount, Spread, Deferment) and join back on the ID number.

 

Both scenarios are mocked up in the attached workflow

Highlighted
14 - Magnetar
14 - Magnetar

Hi @aCha1120 

With a little transpose magic!!

 

image.png

 

Transposing makes the data vertical, which allows us to rearrange. The formula tool fills in "ID" for the heading for column 1, and the company name for the rows where it will otherwise be blank.

A Make Columns tool builds the table again, and they Dynamic Select selects only the Value columns.

 

Dynamic rename to fix the names.

 

Let me know if that helps.

 

Cheers!

Esther

Labels