topic Re: Pricing Model in QlikView

Pricing Model

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 11:22:54 GMT

Try for example an INTERVALMATCH:

INTERVALMATCH(Weight, Service, [Package Type])

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

RESIDENT YourRateTable;

IntervalMatch

Re: Pricing Model

Mon, 13 Jun 2016 14:08:19 GMT

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;

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 14:19:58 GMT

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

Re: Pricing Model

Mon, 13 Jun 2016 14:28:59 GMT

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

Re: Pricing Model

Mon, 13 Jun 2016 14:30:31 GMT

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

Thanks for your help though!

Re: Pricing Model

Mon, 13 Jun 2016 14:35:42 GMT

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

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 14:45:21 GMT

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?

Re: Pricing Model

Mon, 13 Jun 2016 14:54:04 GMT

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

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 15:01:04 GMT

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)

Re: Pricing Model

Mon, 13 Jun 2016 15:33:43 GMT

That is correct. In most cased there is only one "range" as such but I have to have this in place for all suppliers in case they change their model.

I made the change to the range so that it doesn't overlap and I get multiple joins on the table.....

I've seen a post about slowly changing dimensions but I can't see anything that references multiple other fields that need to match

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 15:49:58 GMT

If you still have issues, then please upload your new rates excel sample file with the modifications on the ranges you've made.

Re: Pricing Model

Mon, 13 Jun 2016 16:08:36 GMT

Hi Swuehl

Attached is both the new rate sheet and my QVW - the weights are linking on the level and type but not the weight range ( as you can see In the script I wasn't sure if it was a formatting thing so tried changing that)

Thanks for your help on this.

Re: Pricing Model

swuehl — Mon, 13 Jun 2016 16:27:20 GMT

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.