Can I do contains function when join two tables

[#1]

Hello Everyone,

I have two table. I want to join this two table when Key1 contains Key2.

Table 1
AAAAA
BBBBB
ABABC
BABAB
CABC
DECFG

Table 2
AAA
BB
ABA
DEF
FDC
GHJ

Result	Result
AAAAA	AAA
BBBBB	BB
ABABC	ABA
BABAB
CABC
DECFG

Thank you very much in advance!

[sunny_talwar]

The fourth row BABAB also contains ABA, what is the reason to not have ABA for BABAB?

[Kushal_Chawda]

I think he needs match string from start

[#4]

Sunny - Thank you for your question.

Consider each letter is one level from left to right. We can drill up and drill down on this sequence.

[sunny_talwar]

dust0000 wrote:

Consider each letter is one level from left to right. We can drill up and drill down on this sequence.

I don't think I understand, kushal‌‌ do you?

[#6]

Hi Sunny,

Please see attached excel file for sample data. I changed this sample data.

I want Table1.ID contains Table2.ID.

Thank you very much!

[maxgro · Accepted solution]

Maybe

RESULT

SCRIPT

Table1:

LOAD * Inline [

F1

AAAAA

BBBBB

ABABC

BABAB

CABC

DECFG

];

Join (Table1)

LOAD * Inline [

F2

AAA

BB

ABA

DEF

FDC

GHJ

];

Final:

LOAD *, F1 as F1Check Where FlagContains;

LOAD

  F1,

  F2,

  wildmatch(F1, F2 & '*') as FlagContains

Resident Table1;

Concatenate (Final)

LOAD Distinct

  F1

Resident Table1

Where not Exists(F1Check, F1);

DROP Table Table1;

[sunny_talwar]

Another way similar to maxgro‌'s solution:

MappingTable:

Mapping

LOAD F2,

  '/' & F2 & '\' as F22

Inline [

F2

AAA

BB

ABA

DEF

FDC

GHJ

];

Table1:

LOAD *,

  If(WildMatch(F1, TextBetween(MapSubString('MappingTable', F1), '/', '\') & '*') and Len(Trim(MapSubString('MappingTable', F1))),

  TextBetween(MapSubString('MappingTable', F1), '/', '\')) as F2;

LOAD * Inline [

F1

AAAAA

BBBBB

ABABC

BABAB

CABC

DECFG

];

[#9]

maxgro and Sunny,

You guys are great. This is just want I want.

You guys have great long weekend!

Best regards!