linking range-based pricing table into datamodel

[#1]

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 number	Ship to PLZ	Quantity (KG)
1111111	5251	2000
1111112	5251	5000
1111113	5251	7000
1111114	5251	10000

Delivery cost table

Ship to PLZ	Quantity from (kg)	Quantity to (kg)	Cost per delivery (eur)
5251	1	1000	100
5251	1000	4000	200
5251	4000	8000	300
5251	8000	12000	400

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

[thomaswrieck]

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

[#3]

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

[thomaswrieck]

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

[#5]

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

[thomaswrieck]

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