Joiner node with wild cards

[nicolas_mtam]

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

[lelloba]

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?
  

Raffaello Barri

[takbb]

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:

Join Custom Condition - indexed – takbb

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!

[nicolas_mtam]

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

[nicolas_mtam]

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