Find and highlight the matching values in Table B using Table A

[udayk86081]

Hello, Can you please help me with the below scenario.
Table A has few values in ID field, and
Table B has a Data field
I want the result as shown in Expected_Resulting_Table

Thanks a lot in advance.

Table A:
ID
18499,
18026 ,
16050

Table B:
Data
sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk

Expected_Resulting_Table:
ID, Data
18499, sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf ksadfopupo;jlsd,./,/,.q2sdalkfk18
16050, asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk-
16050, v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadl
16050, 09-v/,/.basdloi16050as98kjsadlk asdkjfh239sdflk`0-9r808-09-v/,/.basdloi16050as98kjsadlk
18499, sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+asdf
18499,sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja
null, sdfkj;lkj;as70qw808cvej'lasdfj18499+;kja_l;kjasdf sdfkj;lkj;as70qw808cvej'lasdfj+;kja_l;kjasdf
18026, ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuas
18026, ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuas
null, ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk ksadfopupo;jlsd,./,/,.q2sdalkfkoiuaskdjflk 18026, ksadfopupo;jlsd,./,/,.q2sdalkfk18026oiuaskdjflk

[Jeevayswaran · Accepted solution]

Hi, please try with mapsubstring function.

[BrunPierre]

Something along these lines.

TableA:
LOAD ID

FROM ABC;

TmpListConcat:
LOAD Concat(Chr(39)&'*'&ID&'*'&Chr(39),',') as List
RESIDENT TableA;

LET vList= FieldValue('List',1);

DROP Tables TmpListConcat, TableA;

TableB:
LOAD Data
If(WildMatch(Data, $(vList)) > 0, Mid(Data, Index(Data, '1'),5), 'Null') as ID

FROM XYZ;

[udayk86081]

Hi @Jeevayswaran 

Thanks a lot. The MapSubString function worked for me.

[udayk86081]

Hi @BrunPierre 

Thank you for taking your time responding to my query. I didn't try your solution, as mapsubstring function worked for me. But, definitely will try your solution and let you know the result.