02-05-2019 02:09 PM
I have calculated intersections of our street geometry. The solution desired would put any street name that touches the interection onto the point. The problem I'm having is when there are more than two (2) streets on the intersection. I've found no aggregation function that would get all of the streets. While I can find the intersections with more than two streets easily, I don't want to populate values on the points manually.
Solved! Go to Solution.
02-06-2019 09:31 AM
Pat, this worked perfectly, after I merged the streets to eliminate duplicate values being returned. However, since I need each result in it's own column, I'm trying to find a FA that replicates the Excel Text to Column function. Since I can have up to four streets in an intersection, I can't just simply do a LEFT and RIGHT extraction. And the strings are of course variable length, so I can't just FIND the commas at a given position. I'm sure there is a way, I just can't find it.
02-06-2019 11:55 AM
Pat, I got it. Did several steps, wound up with the results I neeed.
Step 1: LEFT(Input.LIST_OF_STREET_IDS, FIND(',', Input.LIST_OF_STREET_IDS)-1). This gives me the first value in the list.
Step 2: RIGHT(Input.LIST_OF_STREET_IDS, LEN(Input.LIST_OF_STREET_IDS)-FIND(',', Input.LIST_OF_STREET_IDS)). This intermediate step removes the first value and the comma after it.
Step 2A: IF(FIND(',', Output.Step2)=0, Output.Step2, LEFT(Output.Step2, FIND(',', Output.Step2)-1)). This gives me the second value in the list. It's an intersection, so there are always at least two results.
Step 3: IF(FIND(',', Output.Step2)=0, NULL(Text), RIGHT(Output.Step2, LEN(Output.Step2)-FIND(',', Output.Step2))). This intermediate step removes the second value and the comma after it.
Step 4: IF(FIND(',', Output.Step3)=0, Output.Step3, LEFT(Output.Step3, FIND(',', Output.Step3)-1)). This gives the third value, or a Null if there is not one.
Step 5: IF(FIND(',', Output.Step3)=0, NULL(Text), RIGHT(Output.Step3, LEN(Output.Step3)-FIND(',', Output.Step3))). The final step gives me the fourth value when it occurs, or a Null if not.