Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pricing Model

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

BookingWeightServicePackage Type
15ExpressBox
210EconomyParcel
350ExpressParcel

Table 2

SupplierWeight FromWeight ToServicePackage TypeCost
Supplier A010ExpressBox£5
Supplier B050ExpressBox£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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I've added a QuoteID and also a JOIN to the Intervalmatch to get the data model a bit more slim.

Looks quite ok to me.

View solution in original post

13 Replies
swuehl
MVP
MVP

Try for example an INTERVALMATCH:

INTERVALMATCH(Weight, Service, [Package Type])

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

RESIDENT YourRateTable;

IntervalMatch

Not applicable
Author

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
MVP
MVP

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)?

Not applicable
Author

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

Not applicable
Author

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

Thanks for your help though!

Not applicable
Author

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

swuehl
MVP
MVP

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?

Not applicable
Author

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
MVP
MVP

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)