I have achieved what I want but ended up with a lot of code.
T1:
LOAD *,AutoNumber( RowNo(),Category) as num;
LOAD * INLINE [
Category, vendor, price
A , Jack , 1000
A , Jeff , 200
A , Peter , 202
A , Kate , 2333
A , Joan , 2331
A , John , 3000
B , Jack , 2000
B , Jeff , 3000
B , Alex , 3900
];
Combo:
NoConcatenate LOAD Distinct Category,
vendor as vendor_1,
num as num1
Resident T1;
Join LOAD Distinct Category,
vendor as vendor_2,
num as num2
Resident T1;
Join LOAD Distinct Category,
vendor as vendor_3,
num as num3
Resident T1;
DROP Table T1;
Combo_Duplicate:
NoConcatenate
LOAD *,
indicator1&'|'&indicator2&'|'&indicator3 as indicator;
LOAD Distinct Category,
vendor_1&'|'&vendor_2&'|'&vendor_3 as combo_3_vendor,
num1,num2,num3,
(num1*num1+num2*num2+num3*num3) as indicator2,
num1*num2*num3 as indicator1,
num1+num2+num3 as indicator3
Resident Combo
Where vendor_1<>vendor_2
and vendor_2<>vendor_3
and vendor_1<>vendor_3;
DROP Table Combo;
Combo_Distinct:
NoConcatenate
LOAD Distinct indicator,
Category,
FirstValue(combo_3_vendor) as combo_3_vendor
Resident Combo_Duplicate
Group By indicator,
Category;
DROP Table Combo_Duplicate;
The result is a distinct 3-vendor-combination field:
Category combo_3_vendor indicator
A Jack|Jeff|Joan 10|30|8
A Jack|Jeff|John 12|41|9
A Jack|Jeff|Kate 8|21|7
A Jack|Jeff|Peter 6|14|6
A Jack|Joan|John 30|62|12
A Jack|Kate|Joan 20|42|10
A Jack|Kate|John 24|53|11
A Jack|Peter|Joan 15|35|9
A Jack|Peter|John 18|46|10
A Jack|Peter|Kate 12|26|8
A Jeff|Joan|John 60|65|13
A Jeff|Kate|Joan 40|45|11
A Jeff|Kate|John 48|56|12
A Jeff|Peter|Joan 30|38|10
A Jeff|Peter|John 36|49|11
A Jeff|Peter|Kate 24|29|9
A Kate|Joan|John 120|77|15
A Peter|Joan|John 90|70|14
A Peter|Kate|Joan 60|50|12
A Peter|Kate|John 72|61|13
B Jack|Jeff|Alex 6|14|6
It's still not fully satisfactory, too much code... will be interesting to have a leaner approach.
BR, Peter
