Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (2)
11 Replies

Re: Calculate min value accross a random range

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;

Not applicable

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??

Life is so rich, and we need to respect to the life !!!
Not applicable

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.

Not applicable

Re: Calculate min value accross a random range

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

Re: Calculate min value accross a random range

Like this?

Capture.PNG

Life is so rich, and we need to respect to the life !!!
Not applicable

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

MVP
MVP

Re: Calculate min value accross a random range

Like this?

Capture.PNG

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?

Life is so rich, and we need to respect to the life !!!
Community Browser