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

[settu_periasamy]

try this

T1:
LOAD *,AutoNumber(Floor(Rand()*100),Category) as New;
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
];

NoConcatenate
T2:
LOAD  Category,
Firstsortedvalue(vendor,price) as vendor,
Min(price) as price Resident T1 Where New<=3 Group by Category ;

DROP Table T1;

[#3]

Hi Settu,

This is giving me result of min price per category.

Actually I'm looking for min price per category per 3-vendor-combination, such as:

Category     vendor_combination     min_price

A                 Jack|Jeff|Peter              200

A                 Jack|Jeff|Kate               200

A                 Jack|Peter|Kate            202

.....

vendor combination is a random combination of vendors so the possibility is C(6,3) for Category A.

BR, Peter

[Anil_Babu_Samineni]

What is the expected result you are looking when you talk about Randon??

[#5]

in the original example you have 6 vendors in Category A, I want to calculate min price for whatever possible 3-vendor combination of the 6 vendors.

Category     vendor_combination     min_price

A                 Jack|Jeff|Peter              200

A                 Jack|Jeff|Kate               200

A                 Jack|Peter|Kate            202

.....

the number of possible combinations is C(6,3) for Category A.

[#6]

Hi guys,

Tried below code to make the field vendor_combine

T1:
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
];

Join
LOAD Category,
vendor as vendor1
Resident T1
;

Join
LOAD Category,
vendor as vendor2
Resident T1
;

T2:
NoConcatenate LOAD *,
vendor&'|'&vendor1&'|'&vendor2 as vendor_combine
Resident T1
Where vendor<>vendor1
and vendor1<>vendor2
and vendor<>vendor2; 

But in this case, Jack|Jeff|Joan, Jeff|Jack|Joan, Joan|Jeff|Jack,.... are regarded as 6 different values, but since order doesn't matter here, I want to regard these as only one case. Can anyone help?

BR, Peter

[Anil_Babu_Samineni]

Like this?

[#8]

Like:

A     Jack|Jeff|Peter    200

A     Jack|Jeff|John    200

A     Kate|John|Peter   202

.....

B     Jack|Jeff|Alex    2000

[tresesco]

Like this?

[Anil_Babu_Samineni]

I got it now, Here i am expecting one more point which is How Random vendors are picking. Is there any logic or what?