how to join two table by a fuzzy condition

[#1]

I want to join [tab1] and [tab2] to get result as [out]

but in [tab1], not every x1 is decided by combination of field a and field d, it can be field a and field c or others.

Pls help, thanks a lot

[#2]

Hi, Marco

I tried your solution as below:

SET CustomMatch = if(len(trim($1)) > 0 AND len(trim($2)) > 0, $1=$2, -1); 

tab1:  
LOAD * inline [
st1,st2,st3,st4,st5,st6,st7,it1,cat
1,,,1,,,,,XX1
1,1,1,1,1,,,,XX1-1
2,,,,,,,,XX2
5,,2,,,,,,XX3
];  

tab2:  
LOAD * Inline [
it_1, st1_1,st2_1,st3_1,st4_1,st5_1,st6_1,st7_1
item1,1,1,1,1,1,1,2
item2,2,,,3,1,,,
];

NoConcatenate  
tabTemp:  
LOAD Distinct * Resident tab2;  
left Join  
LOAD Distinct * Resident tab1;  

NoConcatenate  
tabMatches:  
LOAD * Resident tabTemp  
Where  
$(CustomMatch(st1_1, st1)) AND 
$(CustomMatch(st2_1, st2)) AND  
$(CustomMatch(st3_1, st3)) AND  
$(CustomMatch(st4_1, st4)) AND  
$(CustomMatch(st5_1, st5)) AND  
$(CustomMatch(st6_1, st6)) AND  
$(CustomMatch(st7_1, st7)) AND
$(CustomMatch(it_1, it1));  

Left Join (tab2)  
LOAD Distinct  
st1, st2, st3, st4, st5, st6, st7, cat  
Resident tabMatches; 

And then I checked every table.

But it seems not work very well, did I write it wrong?

[#3]

Thank you very much for your help.

I'll try your solution with my real tables and let you know whether it work well.

Thanks again

[MarcoWedel]

you only used parts of my script, so why do you expect the complete result?

regards

Marco

[#5]

I thought the where condition is just to pick up the records that matches between st1~st7 and st1_1~st7_1

Did I misunderstanding?