Dynamically changing column header names with calendar years

Hello

I am having some trouble with how to dynamically change the column header names. I have an annual report that calculates spend per year from the current year to 5 years in the future. So each year my column headers would change with each new report.

Currently the first column would be 2023 and so on until 2027. Next year it would start with 2024 up until 2028. So to bring my data into knime I use a sql script and just added blank columns to the end of my data labeled yr0, yr1, yr2, etc. For this year I will need yr0 column header to be changed to 2023 and then the rest would need to be changed to their respective years. Also, the spend columns at the end would include the ‘year + spend’ so ‘2023 spend’ and so on.

Right now I am using the rule engine to manually change the names after I have rotated the columns into rows , but I was looking for a way for knime to dynamically change the names possibly from just inputting the first year, 2023, in a workflow variable and then calculating the following years from that. Any suggestions would be appreciated! Thank you

Here is a screenshot of the data and the current workflow I have:

Hi @jkrom , I can’t relate your screenshots with what you are saying. I mean you mentioned “current year to 5 years in the future”, it’s unclear if that means current year + 5 years, which would make 6 years, and in your screenshot you have yr0 to yr5, which would be current year + 5 years, but you give the example of “Currently the first column would be 2023 and so on until 2027” which is 5 years (current year + 4 years).

I put something together based on 2023 (current year) to 2027. In any case, that is easily changeable in my workflow. I am also not relying on what the original column names are as you do not explain if they are always YR0, etc, or if that’s just an example.

Here’s my input table (it does not really matter what their column headers are, they go by order):

Results:

Workflow looks like this:

The important thing to look at is the Create Date&Time Range node:

  1. Configure Output Type to Date (It does not really matter even if you leave it to datetime)
  2. Use the Fixed option, and that’s where you can change to 5 or 6 years
  3. If you choose “Use execution date&time” as I am, it will use the current datetime (the time when the node is executed), so this will get you to start with the current year. If for some reasons, you need to run it for different years, you can specify a Start Date instead.
  4. Since you want for 1 year interval, we use the Interval option with value 1Y (1 year).

Here’s the workflow:

5 Likes

Great idea @bruno29a especially the table spec to avoid the unpivoting. Thanks for reminding me about that.

2 Likes

@bruno29a
Thank you very much this will work for me

No problem @jkrom , happy to help

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