Transform/Transpose Data single line of information with Sort

Question

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.

VINFeatXPntCdMY123white hoodCJA2013123Blue Accent BodyCJB2013123Red bodyPNA2013789orange HoodCJD2014789White Accent HoodCJE2014789Less Accent TrunkCJK2014789orange bodyPNM2014001purple HoodCJF2015001pink bodyPNX2015

CURRENT OUTPUT:

VINMY1PaintX_cd1PaintX_cd2PaintX_cd3PaintX_cd40012015purple Hood_Cpink body_P  1232013white hood_CBlue Accent Body_CRed body_P 7892014orange Hood_CWhite Accent Hood_CLess Accent Trunk_Corange body_P

Transforming_Transposing_Paint.yxmd

Claje · Accepted Answer

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.