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

Mixed short form/long form file - how to transform?

EvolveKev
8 - Asteroid

Hi everyone,

 

I've searched and searched but can't quite find an example that fits my data problem.

 

I have a simple file which seems to be a mix of long form and short form data. I'm trying to transform the short form data into long form, but I'm hitting a wall.

 

RecordTypePropertyIDQuickRefIDPropertyNumberInstanceIDTypeDescriptionClassActYrBuiltEffYrBuilt
550090R1000005910-04-022-0700-907505667AGAttached GarageRA219761976
550090R1000005910-04-022-0700-907505667MAMain AreaRA219761976
550090R1000005910-04-022-0700-907505667MA2Main Area 2nd StoryRA219761976
550091R1000015910-04-022-0800-907505732AGAttached GarageRA119761976
550091R1000015910-04-022-0800-907505732MAMain AreaRA119761976
550091R1000015910-04-022-0800-907505732RPSwimming PoolsARP519761976

 

Ultimately, I'm trying to get one row for each property. The "type" and "description" fields need to be transposed so that each description/type is its own column. So if a property has a garage, that column would contain the word, "attached garage." If it doesn't, then the cell would be empty (or read "none" or something like that).

 

So, "property id" would be the unique identifier in this case.

 

I'm almost certain this is a job for the transpose and crosstab functions, but I just can't seem to get my head around it! Any ideas?

 

 

2 REPLIES 2
Claje
14 - Magnetar

Hi,

I wasn't sure how your long form format should look exactly, so I used the example listed below and came up with a column header format.  You can easily change the header format yourself as needed.

 

Take a look at the attached workflow - you were correct that transpose and cross tab is the easiest way to go about something like this.

EvolveKev
8 - Asteroid

Hey @Claje - this is exactly what I was looking for.  I'm dissecting the workflow right now - this is incredible.

 

Thank you so much - I never would have thought about splitting the file. So, you use the summary function to group the duplicate data while the transpose/crosstab path deals with just the columns we want to break out...and then you union everything back together.  In the union tool you rename a duplicate field so we can then remove that duplicate from the overall data.  This is fabulous.

 

Thank you so much (again!).

Labels