join two tables by substring

[Anonymous]

Hello,

I am trying to join two tables by a substring like is shown with a picture below, but I didn't have any luck with it.

Can you please help me.

As you can see in the picture, I want to join Table2 to Table1, where Field C is primary key and a substring to Field A from Table1.

I would appreciate some help and thank you on advance

[hic]

If it always is character 2,3, and 4 from FieldA that should be used, then you can create a new field

  Mid(FieldA, 2, 3) as Key

and make your join on this key.

But I suspect you want a more general solution, and if so, it could be a challenge, I would explore the option of creating a mapping table from table 2 and use mapsubstring() instead of a join.

HIC

[nagaiank]

You may use the following script and the table "Final" has the desired result.

Table1:

Load * Inline [

FieldA, FieldB

abcde,123

fghjk,456

lmnop,789

qrstu,111

];

Table2:

LOAD * Inline [

FieldC, FieldD

rst,222

mno,333

ghj,444

bcd,555

];

Outer Join (Table1) LOAD * Resident Table2;

Drop Table Table2;

Final:

NoConcatenate

LOAD FieldA, FieldB, FieldC, FieldD Resident Table1

where Index(FieldA, FieldC) > 0;

Drop Table Table1;

[tresesco · Accepted solution]

I have worked out something. , yeah, it's a bit tricky.

Table1:

Load * Inline [

FieldA, FieldB

abcde, 123

fghik, 456

lmnop, 789

qrstu, 111

];

Table2:

Load * Inline [

FieldC, FieldD

rst, 222

mno, 333

ghi, 444

bcd, 555

];

Map:

Mapping Load

  FieldC,

  '_'&FieldC&'_'&FieldD as New

Resident Table2;

Final:

Load

  SubField(MapSubString('Map', FieldA), '_',2) as FieldC,

  FieldA,

  FieldB

Resident Table1;

Join

Load

  FieldC,

  FieldD

Resident Table2;

Drop table Table1, Table2;

Note: Under-score ('_') as been taken to separate out the sub strings assuming there would not be any '_' in your key fields.

[Anonymous]

Thank you all very much for your answers and effort. You helped me a lot