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

Return multiple columns based on value in row?

elishapaulsimon
5 - Atom

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:

UniqueIDFirst NameAddressCityStateZip Code78910Last PositionOffset
1Hanna9189 Roberts St.WoodstockGA30188     0
2Dan  866 Summerhouse LaneCanandaiguaNY14424  82
3Rob 301 New Saddle St.NottinghamMD21233   71
4Elizabeth    353 North Beechwood CourtPalm CityFL34990104
5Ross 16 Border St.LoxahatcheeFL33470   71
6Kevin   555 Redwood StreetLakewoodNJ08701 93

 

Output Table:

UniqueIDFirst NameAddressCityStateZip Code
1Hanna9189 Roberts St.WoodstockGA30188
2Dan866 Summerhouse LaneCanandaiguaNY14424
3Rob301 New Saddle St.NottinghamMD21233
4Elizabeth353 North Beechwood CourtPalm CityFL34990
5Ross16 Border St.LoxahatcheeFL33470
6Kevin555 Redwood StreetLakewoodNJ08701

 

Thanks

5 REPLIES 5
Thableaus
17 - Castor
17 - Castor

Hi @elishapaulsimon 

 

Here's a possible solution (if the fields actually respect an order, this would work).

 

possiblesol.PNG

- 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,

elishapaulsimon
5 - Atom

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, 

 

UniqueIDNameAddressCityStateZip_Code
1Address9189 Roberts St.   
1City Woodstock  
1State  GA 
1Zip Code   30188
27  NY 
28   14424
2State866 Summerhouse Lane   
2Zip Code Canandaigua  
37   21233
3City301 New Saddle St.   
3State Nottingham  
3Zip Code  MD 
410   34990
47353 North Beechwood Court   
48 Palm City  
49  FL 
57   33470
5City16 Border St.   
5State Loxahatchee  
5Zip Code  FL 
67 Lakewood  
68  NJ 
69   8701
6Zip Code555 Redwood Street   
Thableaus
17 - Castor
17 - Castor

@elishapaulsimon 

 

Try this one attached now.

 

I actually messed up the First Name field with the Name field, but now everything's correct.

 

Cheers,

elishapaulsimon
5 - Atom

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:

 

UniqueIDFirst NameAddressCityStateZip Code78910Last PositionOffset
1Hanna9189 Roberts St.WoodstockGA30188     0
2DanV-Cp&nex\W)kv8h%{@MwNgn4Ys@6qr*L866 Summerhouse LaneCanandaiguaNY14424  82
3RobQPqKt8)zd&FwJH54301 New Saddle St.NottinghamMD21233   71
4Elizabeth7HxM,:{jkZsw6CV*RV%$&Mb;^E8xz[{./aNSn8N\yMXB%-4wrNfa3/qK=A/m`n3f353 North Beechwood CourtPalm CityFL34990104
5Ross_cLz[F7=ck,/>w'>16 Border St.LoxahatcheeFL33470   71
6Kevin4\W!\pT4ch~MYkBDn:p5=T`%#.Ddnc,Q5u)b;[[,%^FV^nMJ555 Redwood StreetLakewoodNJ08701 93

 

Many thanks,

 

Thableaus
17 - Castor
17 - Castor

@elishapaulsimon 

 

Using the offset column, you can change a bit my workflow and get what you want:

solution2121.PNG

 

- 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,

Labels