Sort Data using Alteryx? Can I make the next row have similar value as the previous row?

[salvador_ian]

Hi Community,

I'm fairly new to Alteryx and can't find a method to reorder data to make sure that the next row contain simillar value from the previous row.

Each row of my sample data contains piping component matched using the same coordinates. I'm trying to make that the next row have the same component as the previous one (the unique Identifier is "RecordID" and "Source_RecordID" columns) connected to a new component.

It means that each "TYPE" appears in the data twice. the only exception are the end points or connecting points which only appears once.

This is the initial data:

RecordID	TYPE	END_POINT1 X	END_POINT1 Y	END_POINT1 Z	Source_RecordID	Source_TYPE	Source_END_POINT1 X	Source_END_POINT1 Y	Source_END_POINT1 Z	Difference	Sum
18	END-POSITION-NULL	-23725	9162	399.7	980	PIPE	-23725	9162	399.7	0	998
20	END-POSITION-NULL	-23725	9252	399.7	670	PIPE	-23725	9252	399.7	0	690
22	END-CONNECTION-EQUIPMENT	-19911	8658.65	5330.7	1295	GASKET	-19911	8658.65	5330.7	0	1317
25	ELBOW	-23882	7319.9	1456.8	55	PIPE	-23882	7319.9	1456.8	0	80
25	ELBOW	-23865.1	7303	1456.8	191	PIPE	-23865.1	7303	1456.8	0	216
55	PIPE	-23882	8561.6	1456.8	99	TEE	-23882	8561.6	1456.8	0	154
99	TEE	-23882	8612.4	1456.8	129	PIPE	-23882	8612.4	1456.8	0	228
99	TEE BRANCH	-23856.6	8587	1456.8	267	PIPE	-23856.6	8587	1456.8	0	366
129	PIPE	-23882	8770.1	1456.8	161	ELBOW	-23882	8770.1	1456.8	0	290
161	ELBOW	-23865.1	8787	1456.8	235	PIPE	-23865.1	8787	1456.8	0	396
191	PIPE	-23042.9	7303	1456.8	1020	ELBOW	-23042.9	7303	1456.8	0	1211
235	PIPE	-23741.9	8787	1456.8	299	ELBOW	-23741.9	8787	1456.8	0	534
267	PIPE	-23741.9	8587	1456.8	329	ELBOW	-23741.9	8587	1456.8	0	596
299	ELBOW	-23725	8787	1439.9	526	PIPE	-23725	8787	1439.9	0	825
329	ELBOW	-23725	8587	1439.9	836	PIPE	-23725	8587	1439.9	0	1165
346	PIPE	-23725	8787	674.55	373	ELBOW	-23725	8787	674.55	0	719
346	PIPE	-23725	8787	900.65	403	FLANGE	-23725	8787	900.65	0	749
373	ELBOW	-23725	8803.9	657.65	611	PIPE	-23725	8803.9	657.65	0	984
389	GASKET	-23725	8787	900.65	403	FLANGE	-23725	8787	900.65	0	792
389	GASKET	-23725	8787	903.85	430	TRAP	-23725	8787	903.85	0	819
430	TRAP	-23725	8787	1048.85	457	GASKET	-23725	8787	1048.85	0	887
457	GASKET	-23725	8787	1052.05	471	VALVE	-23725	8787	1052.05	0	928
471	VALVE	-23725	8787	1142.05	512	GASKET	-23725	8787	1142.05	0	983
512	GASKET	-23725	8787	1145.25	570	FLANGE	-23725	8787	1145.25	0	1082
526	PIPE	-23725	8787	1145.25	570	FLANGE	-23725	8787	1145.25	0	1096
611	PIPE	-23725	9235.1	657.65	654	ELBOW	-23725	9235.1	657.65	0	1265
654	ELBOW	-23725	9252	640.75	670	PIPE	-23725	9252	640.75	0	1324
697	PIPE	-23725	8587	577.75	724	ELBOW	-23725	8587	577.75	0	1421
697	PIPE	-23725	8587	1030.85	754	FLANGE	-23725	8587	1030.85	0	1451
724	ELBOW	-23725	8603.9	560.85	921	PIPE	-23725	8603.9	560.85	0	1645
740	GASKET	-23725	8587	1030.85	754	FLANGE	-23725	8587	1030.85	0	1494
740	GASKET	-23725	8587	1034.05	781	VALVE	-23725	8587	1034.05	0	1521
781	VALVE	-23725	8587	1142.05	822	GASKET	-23725	8587	1142.05	0	1603
822	GASKET	-23725	8587	1145.25	880	FLANGE	-23725	8587	1145.25	0	1702
836	PIPE	-23725	8587	1145.25	880	FLANGE	-23725	8587	1145.25	0	1716
921	PIPE	-23725	9145.1	560.85	964	ELBOW	-23725	9145.1	560.85	0	1885
964	ELBOW	-23725	9162	543.95	980	PIPE	-23725	9162	543.95	0	1944
1020	ELBOW	-23026	7303	1473.7	1050	PIPE	-23026	7303	1473.7	0	2070
1050	PIPE	-23026	7303	5313.8	1078	ELBOW	-23026	7303	5313.8	0	2128
1078	ELBOW	-23009.1	7303	5330.7	1108	PIPE	-23009.1	7303	5330.7	0	2186
1108	PIPE	-19927.9	7303	5330.7	1168	ELBOW	-19927.9	7303	5330.7	0	2276
1168	ELBOW	-19911	7319.9	5330.7	1220	PIPE	-19911	7319.9	5330.7	0	2388
1220	PIPE	-19911	8655.45	5330.7	1268	FLANGE	-19911	8655.45	5330.7	0	2488
1268	FLANGE	-19911	8655.45	5330.7	1295	GASKET	-19911	8655.45	5330.7	0	2563

and Here is the output I'd like to have in alteryx (I manually reordered this):

RecordID	TYPE	END_POINT1 X	END_POINT1 Y	END_POINT1 Z	Source_RecordID	Source_TYPE	Source_END_POINT1 X	Source_END_POINT1 Y	Source_END_POINT1 Z	Difference	Sum
99	TEE BRANCH	-23856.6	8587	1456.8	267	PIPE	-23856.6	8587	1456.8	0	366
267	PIPE	-23741.9	8587	1456.8	329	ELBOW	-23741.9	8587	1456.8	0	596
329	ELBOW	-23725	8587	1439.9	836	PIPE	-23725	8587	1439.9	0	1165
836	PIPE	-23725	8587	1145.25	880	FLANGE	-23725	8587	1145.25	0	1716
822	GASKET	-23725	8587	1145.25	880	FLANGE	-23725	8587	1145.25	0	1702
781	VALVE	-23725	8587	1142.05	822	GASKET	-23725	8587	1142.05	0	1603
740	GASKET	-23725	8587	1034.05	781	VALVE	-23725	8587	1034.05	0	1521
740	GASKET	-23725	8587	1030.85	754	FLANGE	-23725	8587	1030.85	0	1494
697	PIPE	-23725	8587	1030.85	754	FLANGE	-23725	8587	1030.85	0	1451
697	PIPE	-23725	8587	577.75	724	ELBOW	-23725	8587	577.75	0	1421
724	ELBOW	-23725	8603.9	560.85	921	PIPE	-23725	8603.9	560.85	0	1645
921	PIPE	-23725	9145.1	560.85	964	ELBOW	-23725	9145.1	560.85	0	1885
964	ELBOW	-23725	9162	543.95	980	PIPE	-23725	9162	543.95	0	1944
18	END-POSITION-NULL	-23725	9162	399.7	980	PIPE	-23725	9162	399.7	0	998
20	END-POSITION-NULL	-23725	9252	399.7	670	PIPE	-23725	9252	399.7	0	690
654	ELBOW	-23725	9252	640.75	670	PIPE	-23725	9252	640.75	0	1324
611	PIPE	-23725	9235.1	657.65	654	ELBOW	-23725	9235.1	657.65	0	1265
373	ELBOW	-23725	8803.9	657.65	611	PIPE	-23725	8803.9	657.65	0	984
346	PIPE	-23725	8787	674.55	373	ELBOW	-23725	8787	674.55	0	719
346	PIPE	-23725	8787	900.65	403	FLANGE	-23725	8787	900.65	0	749
389	GASKET	-23725	8787	900.65	403	FLANGE	-23725	8787	900.65	0	792
389	GASKET	-23725	8787	903.85	430	TRAP	-23725	8787	903.85	0	819
430	TRAP	-23725	8787	1048.85	457	GASKET	-23725	8787	1048.85	0	887
457	GASKET	-23725	8787	1052.05	471	VALVE	-23725	8787	1052.05	0	928
471	VALVE	-23725	8787	1142.05	512	GASKET	-23725	8787	1142.05	0	983
512	GASKET	-23725	8787	1145.25	570	FLANGE	-23725	8787	1145.25	0	1082
526	PIPE	-23725	8787	1145.25	570	FLANGE	-23725	8787	1145.25	0	1096
299	ELBOW	-23725	8787	1439.9	526	PIPE	-23725	8787	1439.9	0	825
235	PIPE	-23741.9	8787	1456.8	299	ELBOW	-23741.9	8787	1456.8	0	534
161	ELBOW	-23865.1	8787	1456.8	235	PIPE	-23865.1	8787	1456.8	0	396
129	PIPE	-23882	8770.1	1456.8	161	ELBOW	-23882	8770.1	1456.8	0	290
99	TEE	-23882	8612.4	1456.8	129	PIPE	-23882	8612.4	1456.8	0	228
55	PIPE	-23882	8561.6	1456.8	99	TEE	-23882	8561.6	1456.8	0	154
25	ELBOW	-23882	7319.9	1456.8	55	PIPE	-23882	7319.9	1456.8	0	80
25	ELBOW	-23865.1	7303	1456.8	191	PIPE	-23865.1	7303	1456.8	0	216
191	PIPE	-23042.9	7303	1456.8	1020	ELBOW	-23042.9	7303	1456.8	0	1211
1020	ELBOW	-23026	7303	1473.7	1050	PIPE	-23026	7303	1473.7	0	2070
1050	PIPE	-23026	7303	5313.8	1078	ELBOW	-23026	7303	5313.8	0	2128
1078	ELBOW	-23009.1	7303	5330.7	1108	PIPE	-23009.1	7303	5330.7	0	2186
1108	PIPE	-19927.9	7303	5330.7	1168	ELBOW	-19927.9	7303	5330.7	0	2276
1168	ELBOW	-19911	7319.9	5330.7	1220	PIPE	-19911	7319.9	5330.7	0	2388
1220	PIPE	-19911	8655.45	5330.7	1268	FLANGE	-19911	8655.45	5330.7	0	2488
1268	FLANGE	-19911	8655.45	5330.7	1295	GASKET	-19911	8655.45	5330.7	0	2563
22	END-CONNECTION-EQUIPMENT	-19911	8658.65	5330.7	1295	GASKET	-19911	8658.65	5330.7	0	1317

I also attached an excel file where I highlighted the similar values for ease of visualization.
Thank you very much for anyone who will try to solve this. 

Best Regards,
Ian

[CoG]

I'm trying to think if there is a clever way to do this (it feels a lot like an Advent of Code problem), but nothing is immediately jumping to mind. An iterative macro can definitely solve this problem, although you'd need to know some more information because there appears to be some ambiguity in your dataset. For example, why, in your output, does the first record connect to the second via the "Pipe" (ID: 267) and not the "Tee Branch/Tee" (ID: 99)? There is third ID: 99 "Tee" which appears to be what is joined on for sorting later on. So are the identifying fields the ID and Type combination?

Off the top of my head, my approach would be to create an Iterative macro, that starts the "END" points, which I am assuming are always labeled "END-...", and then performing successive joins until nothing remains to build up the sorted list as an output. (The exact order above may not be reproduced without additional information since each block of data is not connected to any other blocks; for example: Record ID: 20 could have been listed first).

[salvador_ian]

Thank you @CoG  for taking the time to reply in my question.

About your question, you are correct to point out the ambiguity regarding the type "Tee". It is correct to assume that the identifying fields is the ID and Type combinations in that case. 

About the Iterative macro, what does that mean? it's my first time hearing about that. (I did a quick search and it seems this is what I need, thank you!)

In the sample data, the possible start/end points are based on the "TYPE", the name could either be labelled as "END-" or have a suffix " Branch" (as you noticed earlier on "Tee Branch")

Using the said Iterative macro, it is fine if the exact order above is not reproduced, where "Record ID: 20 could be first" is not a problem. 

I plan to include an identifier later for the correct order but I'm focusing on this part right now. Would adding that identifier now, make the iterative macro easier to make?