# JOIN with For i loop with Step -1 until find true value

Hi friends
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

Tables
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

dimTIPI

ProductTable

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:

ABC1
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)

hi @Felipereis50
i’m not sure result of my w/flow approach is what you are expecting.

mm.

1 Like

Can you share the script as textfile?

Hi Friend,
I thinks it’s a problem

The code 8516149, the return expected would it be like below.
because will find on the second try.

yes Of course
IF “not true” the code will try the join again with step - 1 (item with 7 len)
IF “not true” the code will try the join again with step - 1 (item with 6 len)
until =TRUE.
Then next item.

``````
Option Explicit

Sub CompararNCM2()

Dim NCM As String
Dim Dicionario As New Dictionary '\\Para usar o Dicionário precisa abilitar a referência "Microsoft scripting runtime"
Dim Cel As Range
Dim Cels As Range
Dim Ultlin As Long
Dim i As Integer

Ultlin = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Set Cels = Sheet1.Range("A2:A" & Ultlin)

For Each Cel In Cels
Dicionario(CStr(Cel.Value)) = ""  '\\Convertendo o valor da célula para String com Cstr
Next Cel

Ultlin = Sheet1.Range("E" & Rows.Count).End(xlUp).Row
Set Cels = Sheet1.Range("E2:E" & Ultlin)

For Each Cel In Cels

For i = 8 To 4 Step -1

If Dicionario.Exists(Left(Cel.Value, i)) Then
Sheet1.Range("B" & Cel.Row).Value = "TRUE"
GoTo Prox:

Else
Sheet1.Range("B" & Cel.Row).Value = "NOT TRUE"
End If

Next i

Prox:

Next Cel

End Sub

``````

DIM_COD_TIPI RESULT COD_TIPI_PRODUCTS
85173033 85173033
8516 85165712
851812 85181223
85191 85191234
851600 85160004
85160005 85160005
85160006 85160006
85160007 85160007
85160008 85160008
85160009 85160009

Hi @Felipereis50 , from reading your requirement, the table supplied by @marzukim looks correct to me.

Surely by your rules, the value “85176149” should match to the exact lookup code “85176149” why do you say it should match “8517614” ?

Have I misunderstood what you meant?

btw, which version of KNIME are you using? If my understanding of the rules is correct, then the new Value Lookup node in 5.1 can greatly simplify this and does not required any kind of looping, whereas in 4.7.x it might be a little more difficult.

Sorry Friend
You’re right.

I looked wrong. (I didn’t see a number at the end of the spreadsheet)

I’m using 5.1.

When you say “Value Lookup”, it’s the node JOIN?

Hi Friend.

Can you explain how you did it?

The “special feature” if this node is strangely hidden and revealed by clicking the “advanced” link at the bottom of the configuration dialog. It enables matching on a “substring”, which is what I suspect @marzukim 's workflow does. If not, I’ll put together a workflow when I have a chance.

I think something like this:

Match on substrings.knwf (91.7 KB)

By sorting into descending length order, and matching on first match, using substring matching, I think it gives that result

4 Likes

Alright…

I will try that node using a large table.

It seems to be simpler than I imagined

1 Like

It will be much more efficient than a loop based approach.

1 Like

@iCFO , I’m sure you will agree that this one new feature of Value Lookup, compared with the Joiner node will likely make it something of a game changer! And I also suspect that mentioning the “advanced” option is going to become quite a common thing on the forum, . I feel it really shouldn’t be hidden away like that.

2 Likes

Agree @takbb! They buried the lead on that one. I didn’t think I would really use the node that much until I noticed the substring functionality.

Looking forward to your webcast! I promise to limit my heckling and spamming of convoluted unanswerable questions in the chat.

2 Likes

Thanks, @iCFO (I think ). Looking forward to it but a lot of the audience for this one will be new to KNIME so I don’t think there’ll be much in there that you don’t know… I’m hoping to be able to squeeze a (small) surprise in at the end though if you stick with it that long, lol.

1 Like

@takbb
A webinar. Great!
The only problem I see is that if you now promote the new nodes KNIME team will never switch back to the “classic” node names
br

1 Like

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