Creating a sequence of columns

Hello Community,

I have a set of data that I want to create new columns in a repeated way.
for example,
column name: name_201203
values: 201203
column name: name_201206
values: 201206

Is there a way to create such a loop that creates all the columns needed?

Another way if possible to put a table that has the column names and values the then create them as the below table.
image

Thanks,

Hello @alidrs ,

here is a workflow I have prepared. Tell me if it works for you.

Have a nice day,
Raffaello

1 Like

Many thanks!

That does work!

One more question, how about if the formula depends on other columns?
for example, instead of just formula as 201203
the value of the formula is:
when “date” <= 201203 then “sales_amount” else null

and the formula for column 201206 would be
when “date” <= 201206 then “sales_amount” else null

and so on

Thanks again!

1 Like

Hello @alidrs ,

can you rephrase your request? Maybe put a small example with screenshots, so I can better picture it :slight_smile:
Raffaello

Hello @lelloba

Attached is the same file you shared with new data with a column expression

as you see, I want to create new columns until paid_203012, more than a 100 columns.
instead of creating a new column for all one by one.

Creating a sequence of columns.knwf (55.6 KB)

Hi @alidrs ,

here’s an updated version of the workflow.

Have a nice day,
RB

1 Like

Thank you for your work! I learned a lot from it!

The issue is that I want the result to be the same as the column expressions that I created. The output data should have the same number of rows as the original data.

Many thanks again!!

2 Likes

I have generated all those extra rows because I have read this:

Anyway, you are welcome :slight_smile:

Have a nice evening,
Raffaello

Hi @alidrs @lelloba , not sure why a loop is needed here…

Isn’t this not just a matter of transpose?

I mean, if I use the same input data that @alidrs gave:
image

And if I transpose, I get this:
image

Then it’s just a matter of moving the first row as headers:
image

Something like this will do:
image

This will run much much faster than using a loop. It does not have to iterate 1 row at a time as the loop does. This way does all at once.

Here’s the workflow: Creating a sequence of columns.knwf (10.9 KB)

And welcome to the Knime Community @alidrs

4 Likes

Hi @bruno29a and @alidrs ,

Bruno is right, a workflow without loops will perform faster.

Thank you for your advice :slight_smile:

Have a nice weekend,
Raffaello

1 Like

Hello @bruno29a

Thank you for the shortcut!

I am still looking for a way to convert a list of formulas that I have.

Column_names Formulas
paid_201203 if(column(paid_date) <= 201203){column(paid_amount)}else null
paid_201206 if(column(paid_date) <= 201206){column(paid_amount)}else null
paid_203012 if(column(paid_date) <= 203012){column(paid_amount)}else null

Best,

Hi @alidrs , you can just apply your Column Expressions to the workflow. Can be done before the Transpose.

This can also be done via the Rule Engine too.

1 Like