This worked !
table1:
LOAD *,trim(text(subfield(Key,'|'))) as kw INLINE [
id, Key, Value1, Value2
3, 70648,50,20 //works for this one !
2, USD|70648, Valuex, valuey
4, USD|70648|xx, Valuex, valuey
1, fxFA | EUR, 20,30
];
left join
LOAD Key,count(DISTINCT kw) as keycount
resident table1
group by Key;
table2:
LOAD * INLINE [
key, otherField
XXX|70648|02, 300
fxFA | EUR, 500
EUR|fxFA|200,200
300|fxFA,10
USD|70648|xx,50
xx|300|USD|70648,100
EUR|500|fxFA,300
AA|70648|05|USD, 500
DD|70648|USD, 200
70648|USD|1919|EST, 150
];
left join(table2)
LOAD id,// as idfk,
kw as kw2 resident table1;
table3:
NoConcatenate
LOAD *,if(WildMatch(key,'*'&kw2&'*'),1,0) as ismatch
resident table2;
drop Table table2;
left join
load key,id,sum(ismatch) as countmatch
Resident table3
group by key,id;
inner join(table3)
LOAD id,keycount as countmatch
resident table1;
table2:
NoConcatenate
LOAD distinct key,otherField,id Resident table3;
drop Table table3;
table1bis:
NoConcatenate
load distinct id, Key, Value1, Value2
resident table1;
drop table table1;
rename table table1bis to table1;
