Pricing Model

[#1]

Dear community,

I have a table of orders which contain information on weight, service and package type.

I want to join these to a rate table that has the service, package type and a upper and lower weight limit. Is there a way I can do a join that matches the service and package type then picks up the correct weight bracket if the weight is greater than the min and less than the max?

Example Below:

Table 1

Booking	Weight	Service	Package Type
1	5	Express	Box
2	10	Economy	Parcel
3	50	Express	Parcel

Table 2

Supplier	Weight From	Weight To	Service	Package Type	Cost
Supplier A	0	10	Express	Box	£5
Supplier B	0	50	Express	Box	£10

What I want to do is add a column for each supplier so Table 1 would have the price for supplier A on line 1 as £5 and a column for supplier 2 for line 1 of £10

Many thanks - Drew

[swuehl]

Try for example an INTERVALMATCH:

INTERVALMATCH(Weight, Service, [Package Type])

LOAD [WeightFrom],[Weight To], Service, [Package Type]

RESIDENT YourRateTable;

IntervalMatch

[#3]

I think you are on to something - any chance you can have a look at my script? I still don't fully understand how the intervalmatch function works.... for my example above. Supplier A would be created 10 times for every weight betweek 0 & 10?

Rates:
LOAD Supplier,
Area,
Service,
prczone,
method,
level,
type,
goodtype,
[Weight Start],
[Weight Finish],
[Min Weight],
[Max Weight],
[Base Cost],
[Additional KG]
FROM
[\\qlikview\Qlikview Documents\Excel Lookup Tables\Rate Comparison.xlsx]
(ooxml, embedded labels, table is [UK Rates]);

Quotes:
LOAD * INLINE [
Weight, level, type
5, 74, parcel
10, 74, parcel
30, 74, parcel
75, 74, parcel
150, 74, parcel
300, 74, parcel
];

TmpBridgeTable:
IntervalMatch(Weight, level, type)
Load distinct
[Min Weight],
[Max Weight],
level,
type
Resident Rates;

[swuehl]

The code should create a link table that for example links Booking 1 to both supplier (because Service and Package Type matches and Weight falls in range interval for both supplier.

If you create a table box with Booking and Supplier, you should see both listed for Booking 1.

If not, then we might need to look at the code / input data. Could you upload some sample records (e.g. your Excel file or a file with same structure / mockup data)?

[#5]

Attached is the "Rates" sheet - at the moment I'm just using that in line load to check it works!

[#6]

In doing this I can see my inline is "parcel" not "Parcel"

Thanks for your help though!

[#7]

With that change my script above only seems to be joining the "level" and "type" and not taking into account the weight breaks

[swuehl]

How are your weight breaks defined in your rate table? I see mostly a weight range 0 to 100000 (sometimes 0 to 10 or 0 to 100, but not clearly defined distinct weight intervals for Min and max Weight?

[#9]

There each "level" and "type" there will be a weight range from 0-10 then 10-100 then 100-1000000 OR it might be 0-20 then 20-100 etc etc. each level and type should have it's own range and then a different range for each supplier

[swuehl]

I don't see this ranges except for supplier F in your sample file, all other supplier always show Min weight 0 for all ranges.

Take care that your intervals don't overlap (i.e. not having a range 0 to 10 and 10 to 100, overlapping at 10), if you want to create a link to a single range. I guess one or the other range should exclude 10 (by adding or subtracting a small number from the one limit)