Loop trough a list of n dates and compare it to a fix value column

KNIME Analytics Platform
1

I have two KNIME tables:

  • **Table 1: Contains a list of ID numbers and the initial allowed charge with the order date.
  • **Table 2: A matrix where first column is ID numbers, the remaining columns represent dates (validity dates) and the corresponding charges. the dates are in the header of the table and the charges are in the rows where present. The number of validity date columns is dynamic and not fixed, we have n columns.

My goal is to update the charge in Table 1 based on the validity dates in Table 2. Specifically:

For each ID in Table 1, I need to check if in table 2 there is a different validity date with his charge. If the order date is later than a validity date in Table 2, the charge in Table 1 should be updated to the charge specified in Table 2 for that validity date. The update should use the latest valid charge before the order date. If the order date is before all validity dates in Table 2, the original charge in Table 1 should remain unchanged.

What is the most efficient way to achieve this in KNIME, considering the variable number of validity date columns in Table 2?
I can also change the structure of table 2.

thank you!

Column 1 Column 2 Column 3 Column 4
ID Allowed Charges Order date
1 20% 01/01/2022
2 2% 01/01/2022
3 4% 01/01/2022
4 7% 01/05/2023
5 15% 01/01/2022
6 23% 01/01/2022
Column 1 Column 2 Column 3 Column 4 E F
ID 12/01/2024 01/01/2024 01/08/2024 01/01/2025 ….
1 7%
2
3
4 6%
5 12%
6
2

Hi,
could it be possible to provide a table with the desired outcome of your example? So I can follow your instructions properly.

As a first guess I would change table 2 to following structure like
ID | Date | Percentage
1 | 12/01/2024 | 7%
4 | 01/01/2024 | 6%

1 Like
3

thank you @ActionAndi
I can change the second table like you mentioned, the problem is that for one ID I could have multiple dates and percentage and the right one depends on the order date. I guess an output could be also an additional column that contains the right percentage of table 2 according to table1 order date, or just a column OK/NOK like in the example below. the problem is to manage multiples dates for the same ID
TABLE 1

Column 1 Column 2 Column 3 Column 4
ID Allowed Charges Order date
1 20% 01/02/2022
2 2% 01/01/2023
3 4% 01/01/2022
4 7% 01/05/2023
5 15% 01/01/2022
6 23% 01/01/2022

TABLE 2

Column 1 Column 2 Column 3 Column 4
ID Date Percentage
1 01/01/2022 45%
1 01/01/2024 40%
2 01/01/2022 2%
3 01/01/2022 4%

OUTPUT TABLE

ID Allowed charges Order date Date Percentage RESULT
1 20% 01/02/2022 01/01/2022 45% NOK
2 2% 01/01/2023 01/01/2022 2% OK
4

Are you sure with the result? According to your description there should be for ID 1 the following the 01/01/2024 as the latest date and the corresponding 40%

image
image962Ă—366 44.3 KB

5

yes I should use the latest valid charge before the order date. for ID 1 the window between 01/01/2022 and 01/01/2024 is the 45% and the order date (01/02/2022) falls into this window.
Basically I have to find a way that says that the “allowed charge”: 20% is wrong. It would be perfect to have it updated every time is wrong but if I get a OK/NOK it’s already a good enough result.
thank you for your hints!