Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

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

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

PolicyNumberDriverAge1DriverAge2DriverAge3

1

20

220
2281923
32227

25

428250
5230

0

62100

AGEDriverAge1DiscountDriverAge2DiscountDriverAge3Discount
0000
201.31.3

1.3

211.51.51.5
221.81.81.8
23222
241.71.71.7
25555
261.81.81.8
27333
28444
etc................
3 Replies
Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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