topic Calculate min value accross a random range in QlikView

Calculate min value accross a random range

Wed, 24 May 2017 10:54:36 GMT

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

settu_periasamy — Wed, 24 May 2017 16:06:53 GMT

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;

Re: Calculate min value accross a random range

Thu, 25 May 2017 01:47:04 GMT

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

Anil_Babu_Samineni — Thu, 25 May 2017 02:01:21 GMT

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

Re: Calculate min value accross a random range

Thu, 25 May 2017 02:07:39 GMT

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

Thu, 25 May 2017 04:48:41 GMT

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;

BR, Peter

Re: Calculate min value accross a random range

Anil_Babu_Samineni — Thu, 25 May 2017 05:03:12 GMT

Like this?

Re: Calculate min value accross a random range

Thu, 25 May 2017 06:05:08 GMT

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

tresB — Thu, 25 May 2017 06:27:29 GMT

Like this?

Re: Calculate min value accross a random range

Anil_Babu_Samineni — Thu, 25 May 2017 16:14:38 GMT

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

Fri, 26 May 2017 01:55:54 GMT

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

Re: Calculate min value accross a random range

Fri, 26 May 2017 02:03:32 GMT

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