How to implement a for loop for this data?

mehappyyou

Let's say I have a mock data that looks like this:

Screenshot 2023-10-12 230758.png

I want to pair a row with RECEIVE AND DELIVER for each order.

For example:

in Order 1, we will have 2 pairs (ID 2 and 4) and (ID 1 and 3).

in Order 2, we will have 1 pair (ID 1 and 3).

In Order 3, we have no pair because both are "DELIVER".

and then filter them so those rows that have a group will be in TRUE.

I am unable to find a way to do this... Any help would be greatly appreciated.

Help

Interface Tools

Iterative Macro

Accepted answers

Prometheus

@mehappyyou If you want the pairs to be side-by-side, you can use this. If you want them to be in the same format as before, you can take the output and split it into two Select tools -- one with the left side of the data and one with the right side of the data and Union them. The 9th row will be left out as it exits the Join at the R anchor.

Pairs.yxmd

All comments

Prometheus

@mehappyyou You can use the Summarize tool to group by ORDER and Count Distinct the values in RECEIVE_OR_DELIVER. After that, you can use a Filter tool to pass counts that are > 1 and then join them back to the original data on ORDER to get only those that have both DELIVER and RECEIVE in the RECEIVE_OR_DELIVER column.

Count Distinct.PNG

Count Greater Than 1.PNG

Join On Order.PNG

Loop.yxmd

mehappyyou

Hi Prometeus,

Thanks very much for your help!

Your logic works perfectly for my current data but if I were to add a 9th row for Order 3 for example:

Screenshot 2023-10-12 230758.png

In the image above, I'd only want to have Record #7 and #9 as they're a pair with Deliver and Receive in Order 3. Record #8 does not have another row with the "RECEIVE" to pair up with so it should not appear in there. Do you know how I can fix this?

apathetichell

Follow @Prometheus 's suggestion - but instead of unioning - join on order number. The resulting data set will show the universe of potential deliever/receive pairs in your dataset.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703