How to find the largest dimension between different fields in script.

[#1]

Hello,

I have the following problem. I load the below tables and when it comes to calculating what the greatest discount  it does not calculate. I have found that when DriverAge3 or DriverAge2 & DriverAge3 is blank I need to replace the blank filed with 0. The 2 tables at the bottom represent the files that are uploaded.

The calculation that does not appear to work is the MaxAgeDiscount I have tried the 2 below if(statements but neither work.

I apologise if I have given too much detail!

Any help is very much appreciated.

Thanks

Gary

Driver:

Load

PolicyNumber,

if(DriverAge1='',0,DriverAge1) AS DriverAge1

if(DriverAge2='',0,DriverAge2) AS DriverAge2

if(DriverAge3='',0,DriverAge3) AS DriverAge3

FROM DriverDetails;

---

LEFT JOIN (DRIVER)

LOAD,

Age as DriverAge1,

Discount AS DriverAge1Discount,

From Loading_Discounts;

---

LEFT JOIN (DRIVER)

LOAD,

Age as DriverAge2,

Discount AS DriverAge2Discount,

From Loading_Discounts;

---

LEFT JOIN (DRIVER)

LOAD,

Age as DriverAge3,

Discount AS DriverAge3Discount,

From Loading_Discounts;

---

DRIVE_DISCOUNT:

Load

Policynumber,

IF(DriverAge3Discount>

(IF(DriverAge1Discount>DriverAge2Discount,DriverAge1Discount,DriverAge2Discount)),DriverAge3Discount,(IF(DriverAge1Discount>DriverAge2Discount,DriverAge1Discount,DriverAge2Discount))) AS MaxAgeDiscount

OR.....

IF(DriverAge1Discount>DriverAge2Discount AND(DriverAge1Discount>DriverAge3Discount),DriverAge1Discount,

IF(DriverAge2Discount>DriverAge1Discount AND(DriverAge2Discount>DriverAge3Discount),DriverAge2Discount,

IF(DriverAge3Discount>DriverAge1Discount AND(DriverAge3Discount>DriverAge2Discount),DriverAge3Discount, ))) AS MaxAgeDiscount

Resident DRIVER

Drop Table (DRIVER)

Drivers

PolicyNumber	DriverAge1	DriverAge2	DriverAge3
1	20	22	0
2	28	19	23
3	22	27	25
4	28	25	0
5	23	0	0
6	21	0	0

AGE	DriverAge1Discount	DriverAge2Discount	DriverAge3Discount
0	0	0	0
20	1.3	1.3	1.3
21	1.5	1.5	1.5
22	1.8	1.8	1.8
23	2	2	2
24	1.7	1.7	1.7
25	5	5	5
26	1.8	1.8	1.8
27	3	3	3
28	4	4	4
etc.	.....	.....	.....

[#2]

hey Gary,

A few things I would do

• Instead of left join go for Applymap()
• To find the max discount value for a policy number use rangemax() function
• you dont have to force zeroes

Rangemax() will ignore text and null values.

Check out the attached doc.

Thanks

AJ

[#3]

HI Ajay

Thank you for your response.

Rangemax(DriverAge1Discount,DriverAge2Discount,DriverAge3Discount) AS MaxAgeDiscount

When I use this in the script it only attached MaxAgeDiscount to the policies that have 3Drivers, is there any was for it to calculate when there is only 1 or 2 drivers.

Thank you very much for your help.

Thanks

Gary

[#4]

RangeMax() would calculate irrespective of how many drivers you have.

Example:

RangeMax(10,2,'World',null()) = 10

So even if you have say only one driver then the remaining two would be null() or zero right, then in that case it will automatically just display the one with a valid integer.All others will be ignored.

If you still have issues can you please attach your qvw.

Thanks