Creating a sequence of columns

[alidrs]

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.

Thanks,

[lelloba]

Hello @alidrs ,

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

Creating a sequence of columns – lelloba

Have a nice day,
Raffaello

[alidrs]

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!

[lelloba]

Hello @alidrs ,

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

[alidrs]

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)

[lelloba]

Hi @alidrs ,

here’s an updated version of the workflow.

Creating a sequence of columns v2 – lelloba

Have a nice day,
RB

[alidrs]

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!!

[lelloba]

@alidrs

he output data should have the same number of rows as the original data.

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

@alidrs

I want to create new columns until paid_203012,

Anyway, you are welcome

Have a nice evening,
Raffaello

[bruno29a]

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:

And if I transpose, I get this:

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

Something like this will do:

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

[lelloba]

Hi @bruno29a and @alidrs ,

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

Thank you for your advice

Have a nice weekend,
Raffaello

[alidrs]

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,

[bruno29a]

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.