5 Replies Latest reply: Oct 21, 2013 5:16 AM by Thomas Rieck RSS

    linking range-based pricing table into datamodel

      Hi

       

      I am building a delivery cost tracking report. We have all deliveries, quantities etc in the system, but we are missing the delivery cost itself. I have in excel file the delivery cost table which I would now need to link to my datamodel. The problem is that cost table is based on ranges and I would like Qlikview to searc the right range and the price.

       

      I made a simple example below about the data I have, which hopefully clarifies my problem

       

      Delivery Table

      delivery numberShip to PLZQuantity (KG)
      111111152512000
      111111252515000
      111111352517000
      1111114525110000

       

      Delivery cost table

      Ship to PLZQuantity from (kg)Quantity to (kg)Cost per delivery (eur)
      525111000100
      525110004000200
      525140008000300
      5251800012000400

       

       

      What kind of script I would need to return delivery cost of 200€ for delivery 111111, 300€ for 111111... ?

       

       

      Thanks already in advance

      Regards

      Janne

        • Re: linking range-based pricing table into datamodel
          Thomas Rieck

          Hi,

           

          maybe this is what you are looking for ?

           

          ShippingSource:

          LOAD * INLINE [

              DeliveryNum, ShippingZip, Qty (KG)

              1111111, 5251, 2000

              1111112, 5251, 5000

              1111113, 5251, 7000

              1111114, 5251, 10000

          ];

           

           

           

           

          RangeSource:

          LOAD * INLINE [

              ShippingZip, Qty (KG) From, Qty (KG) To, ShippingCost, Range

              5251, 1, 1000, 100, A

              5251, 1000, 4000, 200, B

              5251, 4000, 8000, 300, C

              5251, 8000, 12000, 400, D

          ];

           

           

           

           

          Shipping:

          NoConcatenate

          LOAD * Resident ShippingSource;

           

           

          drop table ShippingSource;

           

           

          join IntervalMatch ([Qty (KG)]) LOAD  [Qty (KG) From], [Qty (KG) To] Resident RangeSource;

           

           

          left join

           

           

          LOAD

          [Qty (KG) From],

          ShippingCost,

          Range

          Resident RangeSource;

           

           

          drop table RangeSource;

           

          Best regards

            • Re: linking range-based pricing table into datamodel

              Thanks your reply Thomas, but I think this is not yet solution for me. In example I put in this discussion, I had only one postal code in the pricing table example, but actually I have 93 different postal code in my table. So what the script should do is first search the right set of ranges with the postal code and then within that range the right cost for the quantity delivered. I think the example you made, works only in case of one postal code

               

              Thanks

              Regards

              Janne

                • Re: linking range-based pricing table into datamodel
                  Thomas Rieck

                  Hi Janne,

                   

                  if you have different ranges for zip codes the following adjustments may cover that as well ..

                   

                  join IntervalMatch ([Qty (KG)], ShippingZip) LOAD  [Qty (KG) From], [Qty (KG) To], ShippingZip Resident RangeSource;


                  left join


                  LOAD

                  [Qty (KG) From],

                  ShippingZip

                  ShippingCost,

                  Range

                  Resident RangeSource;

                   

                  Best regards

                  Thomas

                    • Re: linking range-based pricing table into datamodel

                      Hi Thomas,

                       

                      Your addition to script seems to work very nicely, thanks! However, I found out one thing that is causing some complexity and I don't know how to handle that.

                       

                      I have two different set of ranges in my data. In one country there is own ranges and then there is another ranges which is shared by all orher countries. So my question would be, would you have any idea how to integrate some kind of if-formula into the interval match? Script should do sfollowing, in case of this one specific country, do the interval match with range 1 and if it is any other country, do the interval macth in range 2.

                       

                      Would you have idea how to tackle this remaining issue?

                       

                      Many thanks for your great support!

                       

                      Regards

                      Janne

                        • Re: linking range-based pricing table into datamodel
                          Thomas Rieck

                          Hi Janne,

                           

                          glad I could help ...

                          not sure if I understand the new requirement ... the range table contains now :

                           

                          Zip, Country, From, To, Cost ???

                           

                          If thats the case the following should cover this ...

                           

                          join IntervalMatch ([Qty (KG)], ShippingZip, Country) LOAD  [Qty (KG) From], [Qty (KG) To], ShippingZip, Country

                          Resident RangeSource;


                          left join


                          LOAD

                          [Qty (KG) From],

                          ShippingZip,

                          Country,

                          ShippingCost,

                          Range

                          Resident RangeSource;

                           

                          I don't think you need an if statement here. If the above doesn't cover your task it would be helpful If you could provide a qvw that has inline tables with example data.

                           

                          Best regards

                          Thomas