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

    Calculate min value accross a random range

    Peter Quinn

      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 Periyasamy

          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
              Peter Quinn

              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

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

              • Re: Calculate min value accross a random range
                Peter Quinn

                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
                  Peter Quinn

                   

                  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