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

Question

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:

RecordIDTYPEEND_POINT1 XEND_POINT1 YEND_POINT1 ZSource_RecordIDSource_TYPESource_END_POINT1 XSource_END_POINT1 YSource_END_POINT1 ZDifferenceSum18END-POSITION-NULL-237259162399.7980PIPE-237259162399.7099820END-POSITION-NULL-237259252399.7670PIPE-237259252399.7069022END-CONNECTION-EQUIPMENT-199118658.655330.71295GASKET-199118658.655330.70131725ELBOW-238827319.91456.855PIPE-238827319.91456.808025ELBOW-23865.173031456.8191PIPE-23865.173031456.8021655PIPE-238828561.61456.899TEE-238828561.61456.8015499TEE-238828612.41456.8129PIPE-238828612.41456.8022899TEE BRANCH-23856.685871456.8267PIPE-23856.685871456.80366129PIPE-238828770.11456.8161ELBOW-238828770.11456.80290161ELBOW-23865.187871456.8235PIPE-23865.187871456.80396191PIPE-23042.973031456.81020ELBOW-23042.973031456.801211235PIPE-23741.987871456.8299ELBOW-23741.987871456.80534267PIPE-23741.985871456.8329ELBOW-23741.985871456.80596299ELBOW-2372587871439.9526PIPE-2372587871439.90825329ELBOW-2372585871439.9836PIPE-2372585871439.901165346PIPE-237258787674.55373ELBOW-237258787674.550719346PIPE-237258787900.65403FLANGE-237258787900.650749373ELBOW-237258803.9657.65611PIPE-237258803.9657.650984389GASKET-237258787900.65403FLANGE-237258787900.650792389GASKET-237258787903.85430TRAP-237258787903.850819430TRAP-2372587871048.85457GASKET-2372587871048.850887457GASKET-2372587871052.05471VALVE-2372587871052.050928471VALVE-2372587871142.05512GASKET-2372587871142.050983512GASKET-2372587871145.25570FLANGE-2372587871145.2501082526PIPE-2372587871145.25570FLANGE-2372587871145.2501096611PIPE-237259235.1657.65654ELBOW-237259235.1657.6501265654ELBOW-237259252640.75670PIPE-237259252640.7501324697PIPE-237258587577.75724ELBOW-237258587577.7501421697PIPE-2372585871030.85754FLANGE-2372585871030.8501451724ELBOW-237258603.9560.85921PIPE-237258603.9560.8501645740GASKET-2372585871030.85754FLANGE-2372585871030.8501494740GASKET-2372585871034.05781VALVE-2372585871034.0501521781VALVE-2372585871142.05822GASKET-2372585871142.0501603822GASKET-2372585871145.25880FLANGE-2372585871145.2501702836PIPE-2372585871145.25880FLANGE-2372585871145.2501716921PIPE-237259145.1560.85964ELBOW-237259145.1560.8501885964ELBOW-237259162543.95980PIPE-237259162543.95019441020ELBOW-2302673031473.71050PIPE-2302673031473.7020701050PIPE-2302673035313.81078ELBOW-2302673035313.8021281078ELBOW-23009.173035330.71108PIPE-23009.173035330.7021861108PIPE-19927.973035330.71168ELBOW-19927.973035330.7022761168ELBOW-199117319.95330.71220PIPE-199117319.95330.7023881220PIPE-199118655.455330.71268FLANGE-199118655.455330.7024881268FLANGE-199118655.455330.71295GASKET-199118655.455330.702563

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

RecordIDTYPEEND_POINT1 XEND_POINT1 YEND_POINT1 ZSource_RecordIDSource_TYPESource_END_POINT1 XSource_END_POINT1 YSource_END_POINT1 ZDifferenceSum99TEE BRANCH-23856.685871456.8267PIPE-23856.685871456.80366267PIPE-23741.985871456.8329ELBOW-23741.985871456.80596329ELBOW-2372585871439.9836PIPE-2372585871439.901165836PIPE-2372585871145.25880FLANGE-2372585871145.2501716822GASKET-2372585871145.25880FLANGE-2372585871145.2501702781VALVE-2372585871142.05822GASKET-2372585871142.0501603740GASKET-2372585871034.05781VALVE-2372585871034.0501521740GASKET-2372585871030.85754FLANGE-2372585871030.8501494697PIPE-2372585871030.85754FLANGE-2372585871030.8501451697PIPE-237258587577.75724ELBOW-237258587577.7501421724ELBOW-237258603.9560.85921PIPE-237258603.9560.8501645921PIPE-237259145.1560.85964ELBOW-237259145.1560.8501885964ELBOW-237259162543.95980PIPE-237259162543.950194418END-POSITION-NULL-237259162399.7980PIPE-237259162399.7099820END-POSITION-NULL-237259252399.7670PIPE-237259252399.70690654ELBOW-237259252640.75670PIPE-237259252640.7501324611PIPE-237259235.1657.65654ELBOW-237259235.1657.6501265373ELBOW-237258803.9657.65611PIPE-237258803.9657.650984346PIPE-237258787674.55373ELBOW-237258787674.550719346PIPE-237258787900.65403FLANGE-237258787900.650749389GASKET-237258787900.65403FLANGE-237258787900.650792389GASKET-237258787903.85430TRAP-237258787903.850819430TRAP-2372587871048.85457GASKET-2372587871048.850887457GASKET-2372587871052.05471VALVE-2372587871052.050928471VALVE-2372587871142.05512GASKET-2372587871142.050983512GASKET-2372587871145.25570FLANGE-2372587871145.2501082526PIPE-2372587871145.25570FLANGE-2372587871145.2501096299ELBOW-2372587871439.9526PIPE-2372587871439.90825235PIPE-23741.987871456.8299ELBOW-23741.987871456.80534161ELBOW-23865.187871456.8235PIPE-23865.187871456.80396129PIPE-238828770.11456.8161ELBOW-238828770.11456.8029099TEE-238828612.41456.8129PIPE-238828612.41456.8022855PIPE-238828561.61456.899TEE-238828561.61456.8015425ELBOW-238827319.91456.855PIPE-238827319.91456.808025ELBOW-23865.173031456.8191PIPE-23865.173031456.80216191PIPE-23042.973031456.81020ELBOW-23042.973031456.8012111020ELBOW-2302673031473.71050PIPE-2302673031473.7020701050PIPE-2302673035313.81078ELBOW-2302673035313.8021281078ELBOW-23009.173035330.71108PIPE-23009.173035330.7021861108PIPE-19927.973035330.71168ELBOW-19927.973035330.7022761168ELBOW-199117319.95330.71220PIPE-199117319.95330.7023881220PIPE-199118655.455330.71268FLANGE-199118655.455330.7024881268FLANGE-199118655.455330.71295GASKET-199118655.455330.70256322END-CONNECTION-EQUIPMENT-199118658.655330.71295GASKET-199118658.655330.701317

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

Sorting the Data.xlsx

salvador_ian · Answer

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?

CoG · Answer

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).