Loop trough a list of n dates and compare it to a fix value column
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 | | | | | |