Loop to create a new column

KNIME Analytics Platform
,
1

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)

2

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)
2 Likes
3

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

image
image1519×258 60.9 KB

  • 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
1 Like