Hi everyone, I am trying to group up FSAs (first 3 letters of a Canadian postal code) that are next to each other. I tried grouping them based on the distance to each other(less than 3 KM) and then using a multi-row formula to manage deduplication but am running into issues with the duplicates in the two FSAs since I appended FSA to FSA for a many to many join.
Is there a way to manage this in a multi-row formula or is there something else I should try?
My target output is to create a list of unique FSAs with a column called Grouping that would list the main FSA that it is linked to as it is within 3 KM of each other. For example:
Grouping FSA
B1E B1E
B1E B1G
B3H B3H
B3H B3J
B3H B3P
NULL A0A - No FSA that is within 3 KM of it.
I'm including the workflow, a goal output and the input file
@JessicaXChung
If I understand you correctly, you want to create the unique FSA groups that dispite the order of FSAs in each group?
we can do the Sorting and take unique as below.
Hi Qiu, not quite. I want to group up the postal codes that are close to each other(within 3 KM). The problem in the initial summarize I had created is:
For example in the Summarize tool output:
B3H B3J,B3P 2
B3J B3H 1
The goal is for me to have only one row B3H B3J,B3P and to remove B3J B3H. When unsummarized, the output should look like:
Grouping, FSA
B3H, B3J
B3H, B3P
Ideally it would make a row for B3H as well where it belongs to Grouping B3H but I can always make that separately