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

[Felipereis50]

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)

[marzukim]

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

mm.

[Daniel_Weikert]

Can you share the script as textfile?

[Felipereis50]

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.

[Felipereis50]

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

[takbb]

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.

[Felipereis50]

Sorry Friend
You’re right.

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

[Felipereis50]

@takbb

kind of lo

I’m using 5.1.

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

[Felipereis50]

Hi Friend.

Can you explain how you did it?

[iCFO]

Value Lookup

[takbb]

@Felipereis50 ,

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.

[takbb]

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

[Felipereis50]

Alright…

I will try that node using a large table.
Later I will return to you friend.

It seems to be simpler than I imagined

[iCFO]

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

[takbb]

@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.

[iCFO]

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.

[takbb]

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.

[Daniel_Weikert]

@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