Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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:
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;
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: