Calculate min value accross a random range

[#1]

Hi Qlik gurus,

I have a list like:

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

.....

For each Category, I need to pick random 3 vendors, and then calculate the lowest price of these 3 vendors.

So for Category A, there are 20 combinations, and there is 1 lowest price in each,

How to calculate that in script?

BR, Peter

[#2]

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

[#3]

Hi Anil,

Please see my latest reponse below. I think I made it but with too much code... will be interesting to have a leaner approach.

BR, Peter