Column sequence number as a multiplier for column value

Hi … I use the Lag Column node to create a variable number of columns from an input column that is a boolean 1 or 0. In this example, the lag is 6, but it is based on a variable that adds complexity later in the solution.

Now I need to multiply the value in each column by an incremental number so that I get a table that looks like this…

With a limited fixed number of columns, as in this example, I can do it with rule engine nodes but it does not work at all when the number of columns is variable (which it needs to be).

Does anyone have a simple solution?

Thanks,

tC/.

@TigerCole could you use the Math Formula (Multi Column) – KNIME Hub on your flexible number of integer columns? Maybe you could provide us with a sample file that would cover the whole of your challenge.

1 Like

Hi @mlauber71

The example workflow that I have been working on is attached.

Example_TC_ActivityScore.knwf (71.2 KB)

I need to make it much more flexible by getting rid of the Rule Engine nodes.

Thanks.

tC/.

Hi @TigerCole

Can you please confirm your expected output? In your post here, you have the value * the nth column yet in your uploaded workflow you have it reversed.

I have something dynamic right now based on the table dimensions of the numbered columns but it might need some minor adjustment due to the beforementioned discrepancy.

1 Like

Hi @ArjenEX

Sorry that I created the confusion … the workflow is correct. I realized after I published the topic that the inactivity penalty was increasing as it got older but it should decrease as it ages.

tC/.

@TigerCole regardless of increasing or decreasing. I inserted two dynamic elements.

  • a loop that would treat each relevant column on its own thru renaming )not very elegant but it does work)
  • a collection of strings to filter the relevant columns 1,2,3 … in the Multi Column node

kn_forum_48523_dynamic_lag_column_multiplier.knwf (277.1 KB)

Allright @TigerCole. Then my original idea survives :slight_smile: Below is a way to approach it.

Key point is the enforced exclusion/inclusion of certain columns. For example, to determine the amount of numbered columns to deal with which can be initiated with a column filter on type integer.

With a Table Dimension node I then know that the script needs to handle 7 columns. This will be of use later on.

image

For the actual processing of the rows, I opt for a Column List loop with only the numbered columns being in scope (controlled by a Column Splitter node in front). This will ensure the proper processing of the dynamic part in terms of the amount of numbered columns and also limit the amount of data that has to pass through the loop.

What’s helpful about this loop that it automatically keeps track of the column name and iteration as a flow variable.

I opt to replace the rule engine with a column expression using:

if (column(variable("currentColumnName")) == 1) {
    variable("Number Columns") - variable("currentIteration")
} else {
    column(variable("currentColumnName"))
}

The output column is dynamically controlled here as well with a flow variable. Loop output:

A Column Appender node ties all the data back together.

Assuming I change your Integer Configuration from 6 to 7, the workflow will now dynamically account for this:

image

See WF:
Example_TC_ActivityScore Dynamic columns.knwf (201.9 KB)

Hope this provides some inspiration!

4 Likes

Hi @ArjenEX and @mlauber71

My apologies for taking so long to reply. Thank you for your help to resolve this problem. I have a working solution that is really efficient and very robust with any number of columns.

Thank you.

tC/.

3 Likes

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