11 Replies Latest reply: May 25, 2017 10:03 PM by Peter Quinn

# Calculate min value accross a random range

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

• ###### Re: Calculate min value accross a random range

try this

T1:
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:
Firstsortedvalue(vendor,price) as vendor,
Min(price) as price Resident T1 Where New<=3 Group by Category ;

DROP Table T1;

• ###### Re: Calculate min value accross a random range

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

• ###### Re: Calculate min value accross a random range

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

• ###### Re: Calculate min value accross a random range

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.

• ###### Re: Calculate min value accross a random range

Like this?

• ###### Re: Calculate min value accross a random range

Like:

A     Jack|Jeff|Peter    200

A     Jack|Jeff|John    200

A     Kate|John|Peter   202

.....

B     Jack|Jeff|Alex    2000

• ###### Re: Calculate min value accross a random range

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

• ###### Re: Calculate min value accross a random range

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

• ###### Re: Calculate min value accross a random range

Like this?

• ###### Re: Calculate min value accross a random range

Hi guys,

Tried below code to make the field vendor_combine

T1:
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
vendor as vendor1
Resident T1
;

Join
vendor as vendor2
Resident T1
;

T2:
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

• ###### Re: Calculate min value accross a random range

I have achieved what I want but ended up with a lot of code.

T1:
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:
vendor as vendor_1,
num as num1
Resident T1;

vendor as vendor_2,
num as num2
Resident T1;

vendor as vendor_3,
num as num3
Resident T1;

DROP Table T1;

Combo_Duplicate:
NoConcatenate
indicator1&'|'&indicator2&'|'&indicator3 as indicator;
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
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