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!

Hi @bprado

See this wf dynamic_average_calculation.knwf (52.9 KB)

It is uses a Recursive Loop. And every round of the loop the digits are stripped based on the current itaration value of the loop. Matches are collected, non matches are input for the next round of the loop.

See if this works for you.

gr. Hans

4 Likes

Hi !
@takbb has made several components with different joiner options. You can find them on the hub. See : join takbb – KNIME Community Hub

Best,
Joel

4 Likes

Hi @HansS,

I tested the flow, and it worked; however, in the final result, it was keeping the options from Table 2 and excluding the not-found entries from Table 1. I made some adjustments to reverse the loop, which allowed me to retain all options from Table 1 while pulling the averages for the first reference found.

dynamic_average_calculation_ver2.knwf (29.2 KB)

Tks!!

2 Likes

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