Dynamic Average Calculation in KNIME Based on Variable Code Lengths

Hello,

I am working with KNIME and have a question regarding data manipulation between two tables.

I have the following structure:

  1. Table 1: Contains a column called `CODE_NUMBER`, which consists of 7-digit codes.

  2. Table 2: Contains two columns: `CODE_NUMBER` (also 7-digit codes) and `VALUE`.

My goal is to calculate the average of the 

VALUE

for each 

CODE_NUMBER

in Table 1. However, there are specific conditions I need to meet:

  • If the exact 7-digit `CODE_NUMBER` from Table 1 is not found in Table 2, I would like to check for a match using the first 6 digits of the code.

  • If that also does not yield a match, I need to try with the first 5 digits, and continue this process down to a minimum of 3 digits.

I am looking for a way to achieve this without having to perform multiple 

GroupBy

operations and several joins for each digit length.

Is there a more efficient approach in KNIME to accomplish this task?

Thank you for your assistance!