# Grouping city zone by sequentail civic number

hi

i tried to solve by myself but again i have to ask to community.
I have zone and civic number… i have to mark each zone with sequent civic number in the same group, if ther s a zone but a non sequent civic number, the number of the group must be different

I attach the file, the yellow is OUTPUT the green is INPUT. As u can see the result must be in the column 0 the number of the group change when change teh zone OR the zone is not changing but there are not sequantial civic number

I tried with some IF in excel and with join function in string manipulation and use tabel row to variable to loop for each zone, but i can t find a way

Thanks in advice if anyone would help

cheers
STREET_GROUP_u.xlsx (9.8 KB)

Hi @Pippobaudo89, take a look at the Rank node. As an example see the thread below

Does that give you what you need?

If not, you can achieve it using Column Expressions … Specifically see the example workflow n this post

If still not clear, I’ll provide an example when I’m next at my PC.

1 Like

thanks, i tried with rank node 8i forget to mentioned it) but it doesn t match with the condition “switch group when there are no sequantial civic number”)

1 Like

Hi @Pippobaudo89 , I see that I hadn’t fully appreciated the sequence number part. Column Expressions can therefore achieve this. One question though about the sequence civic number. In your Excel data you have some civic numbers which are the same as the previous number, so although it is not “sequential”, you don’t increment the group number.

In which case the rule appears to be that you don’t increment the group if it is the same zone and the civic number either hasn’t changed, or is one more than the previous number.

Is it ever possible for the civic number to go “backwards” within zone, and what should happen if it does?

Assuming that “going backwards” is also reason to increment the Group Number, you can achieve this using the following code in Column Expressions to generate the required groupNumber column:

``````// retain previous row value for Zone and Civic
var prevZone
var prevCivic
var prevSequence

//
if (prevZone==null) {prevZone=""}
if (prevCivic==null) {prevCivic=-1}
if (prevSequence==null) {prevSequence=0}

/* ---- end of initialisation for remembering previous row values */

// detect change of Zone or non-sequential Civic
diffCivic =  column("Civic_integer") - prevCivic
if(column("Zone")!=prevZone || diffCivic > 1 || diffCivic < 0)
{
prevSequence++
}
// retain the zone and civic value for comparison with next row
prevCivic = column("Civic_integer")
prevZone = column("Zone")
// use the retained sequence value for output
prevSequence
``````

2 Likes

dear takbb

you re right because i deleted one column that was the /A, /B of civic number with a rule engine, considering only the civic number as integer and not with also the alphabetic part, i attach the new file with the previous column…

So i tried your code and i will notice you if it s working.
STREET_GROUP_u2.xlsx (10.4 KB)

1 Like

Hi @Pippobaudo89 , I think the above code should achieve it. In the new file, the “Zone” column referenced in the Column Expressions needs to be changed to “Zona”, but I think it is otherwise ok.

1 Like

thanks… i really love this community

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.