Discussion Board for collaboration on QlikView Scripting.
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 2000B Jeff 3000B 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?
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;
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.
What is the expected result you are looking when you talk about Randon??
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.
the number of possible combinations is C(6,3) for Category A.
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?
A Jack|Jeff|Peter 200
A Jack|Jeff|John 200
A Kate|John|Peter 202
B Jack|Jeff|Alex 2000
I got it now, Here i am expecting one more point which is How Random vendors are picking. Is there any logic or what?