Hello Community,
I have this set of data:
Data Set A - Possible Start/End Points
| RecordID | Pairing RecordID | TYPE |
| 99 | 6 | TEE BRANCH |
| 20 | 10 | END-POSITION-NULL |
| 18 | 20 | END-POSITION-NULL |
| 22 | 26 | END-CONNECTION-EQUIPMENT |
Data Set B- Various connected Type(s)
| RecordID | Pairing RecordID | TYPE |
| 25 | 1 | ELBOW |
| 55 | 1 | PIPE |
| 55 | 2 | PIPE |
| 99 | 2 | TEE |
| 25 | 3 | ELBOW |
| 191 | 3 | PIPE |
| 99 | 4 | TEE |
| 129 | 4 | PIPE |
| 129 | 5 | PIPE |
| 161 | 5 | ELBOW |
| 99 | 6 | TEE BRANCH |
| 267 | 6 | PIPE |
| 161 | 7 | ELBOW |
| 235 | 7 | PIPE |
| 235 | 8 | PIPE |
| 299 | 8 | ELBOW |
| 267 | 9 | PIPE |
| 329 | 9 | ELBOW |
| 20 | 10 | END-POSITION-NULL |
| 670 | 10 | PIPE |
| 346 | 11 | PIPE |
| 373 | 11 | ELBOW |
| 346 | 12 | PIPE |
| 403 | 12 | FLANGE |
| 389 | 13 | GASKET |
| 403 | 13 | FLANGE |
| 389 | 14 | GASKET |
| 430 | 14 | TRAP |
| 299 | 15 | ELBOW |
| 526 | 15 | PIPE |
| 430 | 16 | TRAP |
| 457 | 16 | GASKET |
| 457 | 17 | GASKET |
| 471 | 17 | VALVE |
| 471 | 18 | VALVE |
| 512 | 18 | GASKET |
| 373 | 19 | ELBOW |
| 611 | 19 | PIPE |
| 18 | 20 | END-POSITION-NULL |
| 980 | 20 | PIPE |
| 512 | 21 | GASKET |
| 570 | 21 | FLANGE |
| 526 | 22 | PIPE |
| 570 | 22 | FLANGE |
| 329 | 23 | ELBOW |
| 836 | 23 | PIPE |
| 191 | 24 | PIPE |
| 1020 | 24 | ELBOW |
| 611 | 25 | PIPE |
| 654 | 25 | ELBOW |
| 22 | 26 | END-CONNECTION-EQUIPMENT |
| 1295 | 26 | GASKET |
| 654 | 27 | ELBOW |
| 670 | 27 | PIPE |
| 697 | 28 | PIPE |
| 724 | 28 | ELBOW |
| 697 | 29 | PIPE |
| 754 | 29 | FLANGE |
| 740 | 30 | GASKET |
| 754 | 30 | FLANGE |
| 740 | 31 | GASKET |
| 781 | 31 | VALVE |
| 781 | 32 | VALVE |
| 822 | 32 | GASKET |
| 724 | 33 | ELBOW |
| 921 | 33 | PIPE |
| 822 | 34 | GASKET |
| 880 | 34 | FLANGE |
| 836 | 35 | PIPE |
| 880 | 35 | FLANGE |
| 921 | 36 | PIPE |
| 964 | 36 | ELBOW |
| 964 | 37 | ELBOW |
| 980 | 37 | PIPE |
| 1020 | 38 | ELBOW |
| 1050 | 38 | PIPE |
| 1050 | 39 | PIPE |
| 1078 | 39 | ELBOW |
| 1078 | 40 | ELBOW |
| 1108 | 40 | PIPE |
| 1108 | 41 | PIPE |
| 1168 | 41 | ELBOW |
| 1168 | 42 | ELBOW |
| 1220 | 42 | PIPE |
| 1220 | 43 | PIPE |
| 1268 | 43 | FLANGE |
| 1268 | 44 | FLANGE |
| 1295 | 44 | GASKET |
The goal is to organize each record in this way:
1st Step. Get any record from Data Set A (Data Set A is only for starting the process).
2nd Step. Make the 2nd record (from Data Set B) to have the same [Pairing RecordID] but different [Type] as the previous record
3rd Step. Make the 3rd record (from Data Set B) to have the same [RecordID] as the previous record
4th Step. Make the 4th record (from Data Set B) to have the same [Pairing RecordID] but different [Type] as the previous record
5th Step. Make the 5th record (from Data Set B) to have the same [RecordID] as the previous record
...
..
.(repeat until nth time)
nth Step. If there is no matching record in Data Set B, end the loop, and remove the 1st record and (n-1)th record from Data Set A.
(n+1)th Step. Start similar process as 1st step, get record from Data Set A (without the already used values) and start the process again until all the values in Data Set A is used.
I'm fairly new to Alteryx so I'm having difficulty in choosing which set of tools needed for 2nd step and 3rd step, that would allow me make the next row have the same value as the previous row with this given data.
Additionally, I'm currently practicing making an iterative macro but I'm not sure if the conditions I made on nth step and n+1 step would be applicable or I'll need to make a different kind of condition.
Any advice is greatly appreciated.
I'm looking forward to your responses.
Thank you very much!
Best Regards,
Ian
