Loop to create a new column

[Marco_Timperi]

Hi,
I have created the attached workflow which is basically building the hierarchical chain for each of the employees.
I wanted to do this using a loop as the number of iterations is not fixed in the real life example.
In a perfect world I want to stop the iteration for each employee once the CEO (nr.10 in the example attached) is already present in the chain.

Any suggestion?

loop test.knwf (87.4 KB)

[ActionAndi]

It can be easily done in Python. So grab the Python Script Node and enter following

import knime.scripting.io as knio

# This example script simply outputs the node's input table.
# Get Data from KNIME
df = knio.input_tables[0].to_pandas()


# Function for Chaining
def find_chain(start_id, id_map):
    chain = []
    current_id = start_id

    while current_id not in chain:  # Avoid endless loops
        chain.append(current_id)
        next_id = id_map.get(current_id)
        if next_id is None or next_id == current_id:  # End of Chain
            break
        current_id = next_id

    return chain

# Mapping from ID zu MngtID
id_map = dict(zip(df["ID"], df["Mng ID"]))

# Chains for every ID
df["Chain"] = df["ID"].apply(lambda x: find_chain(x, id_map))

# Return to KNIME
knio.output_tables[0] = knio.Table.from_pandas(df)

[ActionAndi]

With this Workflow you can create a nicer table if you like:

• Table Creator: your Input table
• Python Script: script from above → table hold a Collection column
• Split Collection Column → create columns for every part of the collection (similar to your solution)
• Unpivot: stack the column values to have a long table instead of wide table (it’s more handy in my opinion)
• Counter Generation: Add row counter to calculate MngtLevel
• Rank: Rank counter, group by EmployerID → MngtLevel
• Missing Value: remove rows with missing values in MngtID
• Row Splitter: Split table according to MngtLevel top = 1, bottom > 1
• Join the lower table with the source to get the Names of the Manager
• Concatenate both tables
• Create nicer names and sort

[alpayzeybek]

Looks like a great answer, @ActionAndi! It was definitely an interesting challenge from @Marco_Timperi. I developed an alternative dynamic flow that adapts to changes in the number of employees and chain command without affecting the process. Hope this helps!

Here are the screenshots and the flow:

looptestAZ1.knwf (63.2 KB)

Best,

Alpay