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.
RecordType | PropertyID | QuickRefID | PropertyNumber | InstanceID | Type | Description | Class | ActYrBuilt | EffYrBuilt |
5 | 50090 | R100000 | 5910-04-022-0700-907 | 505667 | AG | Attached Garage | RA2 | 1976 | 1976 |
5 | 50090 | R100000 | 5910-04-022-0700-907 | 505667 | MA | Main Area | RA2 | 1976 | 1976 |
5 | 50090 | R100000 | 5910-04-022-0700-907 | 505667 | MA2 | Main Area 2nd Story | RA2 | 1976 | 1976 |
5 | 50091 | R100001 | 5910-04-022-0800-907 | 505732 | AG | Attached Garage | RA1 | 1976 | 1976 |
5 | 50091 | R100001 | 5910-04-022-0800-907 | 505732 | MA | Main Area | RA1 | 1976 | 1976 |
5 | 50091 | R100001 | 5910-04-022-0800-907 | 505732 | RP | Swimming Pools | ARP5 | 1976 | 1976 |
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?
Solved! Go to Solution.
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.
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!).