Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mja_jami
New Contributor III

Nested if condition in load script

Hello Qlik Experts,

I’ve an existing script that is loading data, when I add one more condition generates error. Can anyone help me with the nested condition please? The data input fields are:

policy_type

pfl

premium

livecount

policy_eff

AAA

N

4473.36

55

4/1/2018

ABC

N

1512

50-

4/1/2018

DDD

 

2550

66

9/8/2018

DDD

N

-75

40

9/17/2019

DDD

Y

125

2

1/1/2018

XYZ

N

-1872

50-

1/1/2018

Currently the script I’ve is:

if (policy_type = 'DDD' and policy_eff< '1/1/2019' and pfl <> 'N' and livecount > 50 and premium>0, premium*.85,

if (policy_type = 'DDD' and policy_eff< '1/1/2019' and pfl<>'N' and livecount <= 50 and premium>0, premium*.75,

if (policy_type = 'DDD' and policy_eff>= '1/1/2019' and pfl<>'N' and premium>0, premium*.92, 0))) as pfl_premium

I want to populate a calculated field pfl_premium by loading the records that have fields:

  1. policy_type = ‘DDD’
  2. pfl =not equal ‘N so can be either ‘Y’ or BLANK/ NULL
  3. premium > 0

and generate/ populate a field value pfl_premium for:

  1. policy_eff (dates before) < 1/1/2019:
    1. livecount < 50

pfl_premium: premium * .70

  1. livecount >50

pfl_premium: premium * .80

  1. policy_eff (dates after) >= 1/1/2019:

pfl_premium: premium * .90 (doesn’t matter whatever the livecount)

anything else, populate 0, for example, if premium is -75, populate 0 in pfl_premium, if policy_type = anything but DDD populate 0, if pfl =  ‘N’ populate 0 in pfl_premium.

 

Would you please check my script and help me as I got different result comparing my excel calculation?

I sincerely appreciate your kind help. Thank you very much!

Best regards,

mja

Tags (1)
Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Nested if condition in load script

Perhaps this:

If(policy_type = 'DDD' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
	If(policy_eff< '1/1/2019' and livecount < 50, premium * 0.7,
		If(policy_eff< '1/1/2019' and livecount >= 50, premium * 0.8, 
			0.9))
, 0) as pfl_premium,
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
Highlighted
MVP
MVP

Re: Nested if condition in load script

Something like this:

 

If(policy_type = 'DDD' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
	If(policy_eff< '1/1/2019' and livecount < 50, premium * 0.7,
		If(policy_eff< '1/1/2019' and livecount >= 50, premium * 0.8, 
			0.9)
	, 0),
, 0) as pfl_premium,

Make sure that policy_eff is a valid numeric date field and that the dates like '1/1/2019' are recognized by Qlik as dates (ie the default date format in the Set statements is D/M/YYYY or M/D/YYYY). Otherwise use MakeDate() or Date#() for these dates.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
mja_jami
New Contributor III

Re: Nested if condition in load script

Thank you very much for your help. I see error, possibly with the (,) when I pasted:

PFL Prem calculation Syntax.PNG

and there are two commas here:

),
, 0) as pfl_premium,

Thanks & regards,

Jami

Highlighted
mja_jami
New Contributor III

Re: Nested if condition in load script

Thank you very much. would you please take a look, why am I getting syntax error with the script:

If(policy_type = 'DDD' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
	If(policy_eff< '1/1/2019' and livecount < 50, premium * 0.7,
		If(policy_eff< '1/1/2019' and livecount >= 50, premium * 0.8, 
			0.9)
	, 0),
, 0) as pfl_premium,

PFL Prem calculation Syntax.PNG

while, trying to fix it with:

If(policy_type = 'DBL' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
If(policy_eff< '1/1/2019' and livecount <= 50, premium * 0.75,
If(policy_eff< '1/1/2019' and livecount > 50, premium * 0.85,
0.92)), 0) as pfl_premium, 

my calculation goes off, excel gives me different number than qlik load data.

I sincerely appreciate your time and effort. 

Best regards,

Jami

Highlighted
MVP
MVP

Re: Nested if condition in load script

Perhaps this:

If(policy_type = 'DDD' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
	If(policy_eff< '1/1/2019' and livecount < 50, premium * 0.7,
		If(policy_eff< '1/1/2019' and livecount >= 50, premium * 0.8, 
			0.9))
, 0) as pfl_premium,
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
mja_jami
New Contributor III

Re: Nested if condition in load script

Thank you very much!
Highlighted
mja_jami
New Contributor III

Re: Nested if condition in load script

Happy Friday!

I was wondering where to change if I want to include the negative premiums and do the same calculation. The following script calculates for pfl_premium only when premium > 0. 

If(policy_type = 'DDD' and (pfl <> 'N' Or IsNull(pfl)) and premium > 0,
	If(policy_eff< '1/1/2019' and livecount < 50, premium * 0.7,
		If(policy_eff< '1/1/2019' and livecount >= 50, premium * 0.8, 
			0.9))
, 0) as pfl_premium,

 Now, I need to calculate the (-) premiums too and turn the pfl_premium amount into positive amount with the same calculation. Where can I change?
Please help. Thank you very much.