Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. | ..... | ..... | ..... |
hey Gary,
A few things I would do
Rangemax() will ignore text and null values.
Check out the attached doc.
Thanks
AJ
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
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