Hello Alteryx Community,
I need assistance with figuring out how to fix the following problem below. The data listed is sample data but the data I received is a much larger set with more columns.
From the Input Table, you can see that the addresses were mapped incorrectly past the "Fist Name" column. I create the "Last Position" column and the "Offset" column to figure out how many columns each record is offset by. If the "Last Position" record is empty, the "Offset" returns 0.
Given the values in either the "Last Position" and "Offset" columns, how would I go about returning the records for the columns in the correct column and record order?
Example:
Input Table:
UniqueID | First Name | Address | City | State | Zip Code | 7 | 8 | 9 | 10 | Last Position | Offset |
1 | Hanna | 9189 Roberts St. | Woodstock | GA | 30188 | 0 | |||||
2 | Dan | 866 Summerhouse Lane | Canandaigua | NY | 14424 | 8 | 2 | ||||
3 | Rob | 301 New Saddle St. | Nottingham | MD | 21233 | 7 | 1 | ||||
4 | Elizabeth | 353 North Beechwood Court | Palm City | FL | 34990 | 10 | 4 | ||||
5 | Ross | 16 Border St. | Loxahatchee | FL | 33470 | 7 | 1 | ||||
6 | Kevin | 555 Redwood Street | Lakewood | NJ | 08701 | 9 | 3 |
Output Table:
UniqueID | First Name | Address | City | State | Zip Code |
1 | Hanna | 9189 Roberts St. | Woodstock | GA | 30188 |
2 | Dan | 866 Summerhouse Lane | Canandaigua | NY | 14424 |
3 | Rob | 301 New Saddle St. | Nottingham | MD | 21233 |
4 | Elizabeth | 353 North Beechwood Court | Palm City | FL | 34990 |
5 | Ross | 16 Border St. | Loxahatchee | FL | 33470 |
6 | Kevin | 555 Redwood Street | Lakewood | NJ | 08701 |
Thanks
Solved! Go to Solution.
Here's a possible solution (if the fields actually respect an order, this would work).
- Path to the top - use Select tool to drop columns Last Position and Offset
- Transpose your columns using key fields as Unique ID and First Name, not affected
- Use Data Cleansing tool to force empty values
- Filter empty values
- Use Tile Tool by Unique ID and First name to give the sequence of rows an order
- Path to the bottom - use Select tool to drop all columns with the exception of Address, City, State and Zip Code
- Use Field Info to get Field Names
- Use Record ID to get them in order
Join both paths of data by Tile_Sequence_Num and Record ID
Drop all columns except Unique ID, First Name, Value and Right_Name (which represents the actual names of the fields).
Use Cross-Tab tool grouping by Unique ID and First Name, using Right_Name as New Column Headers and Value as Values for New Columns.
The magic is done!
WF attached.
Cheers,
Hi @Thableaus,
Yes, the fields respect the order they come in. I ran the workflow you mentioned in my machine and got the following result after the final Cross Tab tool. Any clue what's wrong on my end?
I am currently in Version 2018.4.3.54046. Do you think the program version affects the solution/WF?
Thanks,
UniqueID | Name | Address | City | State | Zip_Code |
1 | Address | 9189 Roberts St. | |||
1 | City | Woodstock | |||
1 | State | GA | |||
1 | Zip Code | 30188 | |||
2 | 7 | NY | |||
2 | 8 | 14424 | |||
2 | State | 866 Summerhouse Lane | |||
2 | Zip Code | Canandaigua | |||
3 | 7 | 21233 | |||
3 | City | 301 New Saddle St. | |||
3 | State | Nottingham | |||
3 | Zip Code | MD | |||
4 | 10 | 34990 | |||
4 | 7 | 353 North Beechwood Court | |||
4 | 8 | Palm City | |||
4 | 9 | FL | |||
5 | 7 | 33470 | |||
5 | City | 16 Border St. | |||
5 | State | Loxahatchee | |||
5 | Zip Code | FL | |||
6 | 7 | Lakewood | |||
6 | 8 | NJ | |||
6 | 9 | 8701 | |||
6 | Zip Code | 555 Redwood Street |
Try this one attached now.
I actually messed up the First Name field with the Name field, but now everything's correct.
Cheers,
Hi @Thableaus,
Thanks for providing the solution. The WF works awesome on my end for the provided scenario. Since my original data set is slightly more complicated, how would you handle the problem if the other columns are not empty? I filled in the empty fields with random strings as an example:
UniqueID | First Name | Address | City | State | Zip Code | 7 | 8 | 9 | 10 | Last Position | Offset |
1 | Hanna | 9189 Roberts St. | Woodstock | GA | 30188 | 0 | |||||
2 | Dan | V-Cp&nex\W)kv8h% | {@MwNgn4Ys@6qr*L | 866 Summerhouse Lane | Canandaigua | NY | 14424 | 8 | 2 | ||
3 | Rob | QPqKt8)zd&FwJH54 | 301 New Saddle St. | Nottingham | MD | 21233 | 7 | 1 | |||
4 | Elizabeth | 7HxM,:{jkZsw6CV* | RV%$&Mb;^E8xz[{. | /aNSn8N\yMXB%-4w | rNfa3/qK=A/m`n3f | 353 North Beechwood Court | Palm City | FL | 34990 | 10 | 4 |
5 | Ross | _cLz[F7=ck,/>w'> | 16 Border St. | Loxahatchee | FL | 33470 | 7 | 1 | |||
6 | Kevin | 4\W!\pT4ch~MYkBD | n:p5=T`%#.Ddnc,Q | 5u)b;[[,%^FV^nMJ | 555 Redwood Street | Lakewood | NJ | 08701 | 9 | 3 |
Many thanks,
Using the offset column, you can change a bit my workflow and get what you want:
- In the transpose tool configuration you include Offset as a key field
- Use a formula tool to do this math: Tile_Sequence_Num - Offset
WF attached.
Cheers,