Nested if condition in load script

[mja_jami]

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

[jonathandienst]

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.

[mja_jami]

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

and there are two commas here:

),
, 0) as pfl_premium,

Thanks & regards,

Jami

[mja_jami]

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,

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

[jonathandienst · Accepted solution]

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,

[mja_jami]

Thank you very much!

[mja_jami]

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.