How to calculate the latest two column difference

[Chenfang]

H Knime experts
I have below data, which will be extent weekly.
This week, I have 6weeks’ data, but I need get the delta between current ww and last ww (it’s ww05)
Next week, I will have 7weeks’ data, I also need get the delta between current ww and las ww(it’s ww06)
Appreciate somebody can help me how to set the node.

1. Always get the delta between current wk and last week
2. How can also set current ww to " ww + number" (Such as this week should be ww06, then next week, when I extend the data, the current ww change to ww07)

This week
	WW01	WW02	WW03	WW04	WW05	Curren ww	Delta(Current WW-ww05)
Part A	10	9	7	7	15	10	-5
Part B	12	8	9	14	12	12	0
Part C	14	11	16	17	12	14	2
Part D	6	3	4	7	5	6	1
Total	42	31	36	45	44	42	-2
Next week
	WW01	WW02	WW03	WW04	WW05	WW06	Curren ww	Delta(Current WW-ww06)
Part A	10	9	7	7	15	10	13	3
Part B	12	8	9	14	12	12	10	-2
Part C	14	11	16	17	12	14	15	1
Part D	6	3	4	7	5	6	4	-2
Total	42	31	36	45	44	42	42	0

[bruno29a]

Hi @Chenfang , you did not explain what’s the logic behind the naming of the column names.

For example, what happens for 2 digits week values? Do they also get prefixed with a 0? For example, would week 11 be WW011 or WW11?

And how are these weeks calculated? Do they ever reset, or can they go beyond 99? For example, can you have WW123 or is 52 maximum?

Regardless, I think you can rely on the column index if you want to find the difference between the last 2 columns. You can refer to columns by their index using the column() function in Column Expressions.

To get the index of the last 2 columns, you can use the Extract Table Dimension to get the number of columns from the table. And since index start from 0, then the last 2 indexes will be number_of_columns - 2 and number_of_columns - 1.

Here’s a quick demo:

This is my input data (same as your week 6):

And I get 7 as number of columns which I store in a variable:

The expression for the Column Expressions is quite easy after that:

idx_current_wk = variable("Number Columns") - 1
idx_last_wk = idx_current_wk - 1

column(idx_current_wk) - column(idx_last_wk)

Results (Same as what you got):

This will work with any given table, so it will work for any given week.

Regarding the renaming of the column, since you do not provide much info about your input data or explain the rules for the naming convention, I did not set up anything for this. However, you can rely on the number of columns, and if I assume that there is always the first column (the one that contains Part A, Part B, etc), then the current week will be number_of_columns - 1.

You can then build the name using join() and with padLeft() to prefix with 0 for single digits (that is if that’s the rule).

You can then rename the column “Current ww” to the new name that was built.

[Chenfang]

Hi Bruno
Thanks for your quickly reply, let me understand your suggestion at first.
It’s only 2 digits week values and max. is ww52.

[Chenfang]

@Chenfang

It’s only 2 digits week values and max. is ww52.

@bruno29a Your solution is very perfect and solved my 1st question.
Would you pls help to show me how to rename the current week, thanks I

[bruno29a]

Hi @Chenfang , I already explained what my approach would be

@bruno29a

you can rely on the number of columns, and if I assume that there is always the first column (the one that contains Part A, Part B, etc), then the current week will be number_of_columns - 1.

You can then build the name using join() and with padLeft() to prefix with 0 for single digits (that is if that’s the rule).

You can then rename the column “Current ww” to the new name that was built.

So basically:

Input (same as yours):

Output:

The new name is generated via the Variable Expressions:

join("WW", padLeft(string(variable("Number Columns") - 1), 2, "0"))

The Column Rename should be configured as this:

[Chenfang]

@bruno29a

Thanks a lot for your kindly help.

Your suggestion has been used successfully.

[bruno29a]

You are most welcome @Chenfang , I’m happy to help