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.
VIN | FeatX | PntCd | MY |
123 | white hood | CJA | 2013 |
123 | Blue Accent Body | CJB | 2013 |
123 | Red body | PNA | 2013 |
789 | orange Hood | CJD | 2014 |
789 | White Accent Hood | CJE | 2014 |
789 | Less Accent Trunk | CJK | 2014 |
789 | orange body | PNM | 2014 |
001 | purple Hood | CJF | 2015 |
001 | pink body | PNX | 2015 |
CURRENT OUTPUT:
VIN | MY1 | PaintX_cd1 | PaintX_cd2 | PaintX_cd3 | PaintX_cd4 |
001 | 2015 | purple Hood_C | pink body_P | ||
123 | 2013 | white hood_C | Blue Accent Body_C | Red body_P | |
789 | 2014 | orange Hood_C | White Accent Hood_C | Less Accent Trunk_C | orange body_P |
Solved! Go to Solution.
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.
thank you! I'll apply that now.