cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Transform/Transpose Data single line of information with Sort

Highlighted
Asteroid

Hello,

I have the following data, and I want the output to be on a separate line.  I am almost there, but my issue is sorting the output.

I just want a single line for the VIN with the multiple paint descriptions displayed, but I want the Body color to be the first displayed (PNTCd begins with a P).  I am stuck on getting the first column listed the Body paint.  Any ideas?  I attached my working YXMD.

VINFeatXPntCdMY
123white hoodCJA2013
123Blue Accent BodyCJB2013
123Red bodyPNA2013
789orange HoodCJD2014
789White Accent HoodCJE2014
789Less Accent TrunkCJK2014
789orange bodyPNM2014
001purple HoodCJF2015
001pink bodyPNX2015

 

CURRENT OUTPUT:

VINMY1PaintX_cd1PaintX_cd2PaintX_cd3PaintX_cd4
0012015purple Hood_Cpink body_P  
1232013white hood_CBlue Accent Body_CRed body_P 
7892014orange Hood_CWhite Accent Hood_CLess Accent Trunk_Corange body_P
Pulsar
Pulsar

Hi,

I've attached an example that accomplishes this.  Basically all I added to your flow was a sort order on the first character of PntCd.  I used a "SWITCH" statement, defaulting to 2, to set the value to 1 for any PntCd that starts with a P.  I added this sort as a key field to the Transpose, and included it in the sort.

The advantage of this solution is that you could swap the default to something like 999, then you could do multiple hierarchies if you had P, C, and E codes, as an example.

Asteroid

thank you!  I'll apply that now.

Labels