Field Value exists in an other field with 'like' comparaison

[t_donnet]

Hi,

I am struggling with a simple request.

Table A is the main table with clean data.

Table B need to refer to table A. The field 'Name' is the only possible association.

We must compare each value from field 'Name' Table A to each value that has in field 'NameB' Table2.

I tried with EXISTS and LIKE (with wildcards) together, no success. Even with a WILDMATCH. 

Any way  to make it easy in scripting. 

Example:

Table A:
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David

Table B:
ID, NameB
12, TEXT Tina
22, TEXT Bryan
28, TEXT James
34. TEXT Harry 1
45, TEXT Alan

Excepting Result:

TABLE Z:
1, James
2, Harry
3, Sara
4, Tina
5, David
12, Tina
28, James
34, Harry

[mato32188]

Hi T_donnet,

hope you are doing well. Please try following:

tableA:
load * Inline[
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David
];

outer join (tableA)

tableB:
load *
where not Exists(Name);

load
Id,
subfield(NameB,' ',2) as Name

Inline[
Id, NameB
12, TEXT Tina
22, TEXT Bryan
28, TEXT James
34, TEXT Harry 1
45, TEXT Alan
];

BR

Martin

[t_donnet]

Hi Martin,

Thanks for your answer.

The thing is for values in the field 'NameB', there aren't structure like the example.
It can be anywhere. (ABVD-Tina_dhsfd12 ; 1298 asdf_Harry; James 123_Copi)

I tried with => Name LIKE *NameB* 
It didn't work

[mato32188 · Accepted solution]

Hi T_donnet,

right, let's try to add some data cleansing and then do the rest of job.

TableA:
load * Inline[
Id, Name
1, James
2, Harry
3, Sara
4, Tina
5, David
];

TableB:
load *
Inline[
Id, NameB
12, ABVD-Tina_dhsfd12
22, 1298 asdf_Brian
28, James 123_Copi
34, TEXT Harry 1 dfaf
45, TEXT Alan
];

//data transformation and cleansing
//autoconcatenate table A
for i=0 to NoOfRows('TableA')-1

let sourceName = '*'&Peek('Name',$(i),'TableA')&'*';

for j=0 to NoOfRows('TableB')-1


TableC:
load Distinct
Id, Replace('$(sourceName)','*','') as Name
Resident TableB
where WildMatch(NameB,'$(sourceName)');

next j;

next i;

drop tables TableB;

EXIT SCRIPT;

Best regards,

Martin

[t_donnet]

Hi Mato,

I was thinking about this double loop this weekend.

Glad you find a good way for my problem.

Thanks a lot

Sincerly,

Theo