Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

5 Replies
thomaswrieck
Contributor

Re: linking range-based pricing table into datamodel

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

Not applicable

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

thomaswrieck
Contributor

Re: linking range-based pricing table into datamodel

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

Not applicable

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

thomaswrieck
Contributor

Re: linking range-based pricing table into datamodel

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

Community Browser