Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks a lot. The MapSubString function worked for me.
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.