I would like some help.
The explanation it’s not too complicated and I will try to explain with precision.
Goal: I want to do a Join, but if the value is not found then try again reducing the digit by -1, try again until you find it
I have two tables.
One table is my dim table with a lot of codes. The name is dimTIPI
The second table is a product table. The name is ProductTable.
These are the tables
Note that if I do a regular JOIN with ProductTable being the first table and dimTIPI being second table, using LEFT JOIN with the columns COD_TIPI x COD_TIPI (two tables)
and returning the “description” column,of course It will be a math for some values.
But, what a want to do is a bit different:
Imagine the first row from the ProductTable,
ABC1 85171431, and the formula "IF join return false try COD_TIPI - 1 from right to left
In other words:
Try 85171431 = false
Try 8517143 = True then return description
Note: there is a rule for the COD_TIP. The max characters is 8 and the minimum is 4.
The dimTIPI table, it will have 8 to 4.
The ProductTable it will have always 8.
We have to try 8 to 4 Step - 1
Let’s see another example:
Look to the ABC5 85175149 from ProductTable
Look to the dimTIPI and you will see only 4 digits
The goal is to do a Join, and of course, the first loop will be false, and only the 4 loop will be True.
PS: I have a code in VBA that do what I explain above, but, how to do in Knime?
Tables.xlsx (10.6 KB)