Compare a row to every other row?

[OrasiPythonGuy]

I have a data set where I'm trying to find the largest distance between any 2 zipcodes that are associated with each account. So essentially for each group I need to compare the distance between every possible combination of zipcodes associated with the account and then find the maximum. How would I go about doing this? I looked into using an iterative macro and then comparing [Zipcode]-[Row-[IterationNumber]:Zipcode], but I couldn't find a way to actually subtract by a variable inside of a multi-row formula.(that example there does not work). Also as far as I know that would keep outputting to the same new column and wiping the old data. Is there an easier way to go about this? What am I missing? Even if someone can just point me in the right direction I would very much appreciate it.

[MichalM · Accepted solution]

@OrasiPythonGuy 

I'd use the JOIN tool and join the data set to each other on the Account ID. You will end up with all possible zip pairings side by side. You can then calculate a distance between them followed by Summarize tool to get the max distance.

[danilang]

Hi @OrasiPythonGuy 

If you do a self-join on the table on ClientID and make sure that the right and left zip codes are in the join output, you'll get a Cross Join by client.  Every zip code will be matched with every other zip for each client.  From there, find the distance from ZipCode to Right_ZipCode in each row and take the largest for each client.

Let me know if your need more details.

Edit:  What @MichalM said, but without the pictures

Dan

[OrasiPythonGuy]

This worked perfectly, but I have a new problem now. When I use the summarize tool to find the max distance, how do I keep the zipcodes associated with that distance in the output? It wants to remove all the fields except the account ID and distance

[MichalM]

If you select the Zip column and do a Group by followed by Max on the Distance this should give you what you need.