Joiner node with wild cards

Hi KNIME community!

I’m trying to perform a one-to-many join using a “Joiner” module but with some wild cards.

My left table is specified with some values being “*” wild card to avoid any maintenance on the modalities of that dimension. I can change the value if necessary but I’m struggling to make my join.

I included a simplified example below with the expected output:

Table 1

Country City Item
Germany Berlin A
Germany * *
* * A
* Milano *

Table 2

Country City Item Status Quantity
Germany Berlin A OK 2
Switzerland Zurich B KO 3
France Paris A OK 4
Italy Milano C KO 1
Germany Berlin A KO 5
Switzerland Basel C OK 3
France Lyon D OK 4
Italy Roma A OK 1

Table output

t1.Country t1.City t1.Item t2.Country t2.City t2.Item t2.Status t2.Quantity
Germany Berlin A Germany Berlin A OK 2
Germany Berlin A Germany Berlin A KO 5
Germany * * Germany Berlin A OK 2
Germany * * Germany Berlin A KO 5
* * A Germany Berlin A OK 2
* * A France Paris A OK 4
* * A Germany Berlin A KO 5
* * A Italy Roma A OK 1
* Milano * Italy Milano C KO 1

Any ideas how to achieve that?

Thanks,
Nicolas

Hi @nicolas_mtam ,

two questions:

  • if you are speaking about cities, why should you consider both country and city name and not just city name?
  • take the case of Milano in your example: in the first table there is item=* and in the second table item is C. Are you sure you want to join the corresponding rows? Example: what happens with a case like the one in the image below?
    image

Raffaello Barri

Hi @nicolas_mtam , you can’t use wildcards with the joiner nodes, so you’ll generally need to perform a Cross Join and then apply a Rule Based Row Filter with the required rules to match/filter each of the returned rows.

Alternatively you can achieve the stated output using my Join Custom Condition - indexed component:

Attach the first of your tables to the top, and the second table to the bottom. It uses SQL, so unfortunately your wildcards need to be adapted slightly to work, but you can configure the component as follows and it should do the job:

t2."Country" like replace(t1."Country",'*','%')
and t2."City" like replace(t1."City",'*','%')
and t2."Item" like replace(t1."Item",'*','%')

btw, Welcome to the KNIME Community! :slight_smile:

1 Like

Hi lelloba,

I agree I haven’t designed this simplified example in the most clever way… In my real situation, it’s two dimensions that can’t be related and completely independent (not like Country and City).

The output would be:

t1.City t1.Item t2.City t2.Item
Milano * Milano A
Milano * Milano B

Thanks,
Nicolas

Hi takbb,

Thank you for your answer!

I was afraid of having to do it with a Cross Join and then a Filter but I see I haven’t other choices.

Very nice component! I’ll definitely have a look at it!

Thank you!
Nicolas

1 Like

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