matching records

[pala_jagadeesh]

Hi i have two tables

table1:

SP	no1
abc;bca;caa;acd	23
acc;add;jhc;ooo	24
fff;ddd;sss;	28

table2:

ID	SR	no2
1	abc;kkk;acd	26
2	add;ooo;www;add	27

But I want output like below

ID	SR	no2	no1
1	abc	26	23
1	kkk	26
1	acd	26	23
2	add	27	24
2	ooo	27	24
2	www	27

[Or · Accepted solution]

Subfield each table individually and left join the result should work:

Load ID, no2, subfield(SR,';') as SR

From Table2;

Left join

Load no1, subfield(SP,';') as SR

From Table1;

[Lakshminarayanan_J · Accepted solution]

@pala_jagadeesh 

Please find the below script and screenshot for your reference.

Script: 

T1:
Load * inline [
SP ,Value1
abc;bca;caa;acd ,23
acc;add;jhc;ooo ,24
fff;ddd;sss; ,28
];

T2:
NoConcatenate
Load
subfield(SP,';') as SP,
Value1
Resident T1;
drop Table T1;

T3:
load * Inline [
ID,SR,Value2
1,abc;kkk;acd,26,
2,add;ooo;www;add,27
];

T4:
Left Keep(T2)
Load
ID,
subfield(SR,';') as SP,
Value2
Resident T3;
drop Table T3;

Result Screenshot: