Joining two tables with wildcard

[#1]

Hello,

I have the following data structure (only extract from data). I would like to match the tables based on the lookup values in the Table 'Lookup'. Both numbers in the 'Lookup' table need to be included in the 'Data' table. As the 'Data' tables shows there can be non-matching numbers before, behind or in between the string.

The proper result would be the following two lines that do include both numbers.

2014405|2014419|2014479, 1
2014405|2014476|2014480|2015185, 1

Thanks for your help!

Lookup:
LOAD * Inline [
Field1
2014405|2014479
2014405|2014480
.... ];


Data:
LOAD * Inline [
Field2, Quantity
1976337|2014405, 1
2014405|2014419|2014479, 1
2014405|2014476|2014480|2015185, 1
....];

[swuehl · Accepted solution]

Maybe like

Lookup:

LOAD * Inline [

Field1

2014405|2014479

2014405|2014480

];

MAP1:

MAPPING

LOAD Subfield(Field1,'|',1), '/1\' as F2

Resident Lookup;

MAP2:

MAPPING

LOAD Subfield(Field1,'|',2), '/2\' as F2

Resident Lookup;

Data:

LOAD *

WHERE SubStringCount(Mapped,'/1\') AND SubStringCount(Mapped,'/2\');

LOAD MapSubString('MAP2',MapSubString('MAP1',Field2)) as Mapped, *;

LOAD * Inline [

Field2, Quantity

1976337|2014405, 1

2014405|2014419|2014479, 1

2014405|2014476|2014480|2015185, 1

];

[#3]

Works Thanks for your help.