Grouping city zone by sequentail civic number

[Pippobaudo89]

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)

[takbb]

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

ID generation

Hi @4nak1n , Welcome to the KNIME community. The Rank node can do this for you. see below: [image]

Does that give you what you need?

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

Column Expressions have Long Memories too!

OH… WELL THIS IS EMBARASSING! I was wrong about the capabilities of the Column Expressions node. The implication of this is: @iCFO will no longer be pointing people to my Java Snippets (although I always enjoyed the challenge! ) @ArjenEX will be able to proclaim that Column Expressions can achieve whatever needs to be achieved (unless of course you want to do it quickly, in which case… Java Snippets all the way… ) Some background… While researching/creatin…

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

[Pippobaudo89]

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”)

[takbb]

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

[Pippobaudo89]

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)

[takbb]

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.

[Pippobaudo89]

thanks… i really love this community