Rank in a group with IF conditions - level: extreme

KNIME Analytics Platform
1

Hello

i would like to ask if someone can solve my “puzzle”.

I need to rank (1 or 2) some contracts per customer by importance under certain conditions. For example Customer A has 3 Contracts and i need to rank them only one by 1 and the other two by 2.

I write the conditions, i tried with groupby and ranking but i don t know how to insert IF conditions not only for some values in cells but also for the number of rows

The conditions are:

-hyerarchy of column “Category” (i ranked by one my custom tabel)

  • number of rows
  • area in m^2

It is possible that the contracts may be in other streets. For example i have 2 contracts for via Pippo 1 and 2 and another 2 contracts for via missoni 1. In this case i have to put two “1” (one for via pippo 1 and the other one for via missoni 1) and two 2.

I don t have problem to calculate the ranking conditions (number of rows i used grupby, hyerarchy of “category” i use rank node), i have problem to use IF with area (IF this area is bigger than the other, wins) and i have a problem to concatenate all of this conditions to determine which contracts is 1 or 2.

I hope to be clear, ask if need (i know it s very difficult). in attach there s an example

thanks in advance

byez
example.xlsx (10.5 KB)

2

Hi,
As I understand it your example only contains the input data. Could you also share the expected output? That would help to understand the problem better.
Kind regards,
Alexander

3

example_REV.xlsx (10.9 KB)

here it goes

thanks so much

the conditions are:

same id customer with different id contracts:

same street-> same category → area in m^2 WINS (so the id contract with the highest m^2 is 1 even if 2 has more rows)

same street → different category → this is the case where i ranked in a table the order of priority to make win one category on another and to decide if the ID contract is 1 or 2

same street → same category → area in m^2 is the same → id contract with more rows WINS (so it will be 1 and the other/s 2)

i know it s complicated but i can t make a concatenate of IF with the cases above

Thanks in advice

cheers

4

@Pippobaudo89 these things come to mind:

  • it might be best to use a local H2 database with Window functions in oder to establish a very flexible ranking, but you still would have to come up with a sequence of rules
  • if it gets more complicated you could opt for assigning Fibonacci numbers for each rule while the numbers would represent the importance. In the end you can add up the numbers (per customer and contract?) and will have a clear ‘path’ of importance. But this might be over-engineering this

Here are the examples to use H2.

2 Likes