topic Re: Nested if condition in load script in QlikView

Nested if condition in load script

mja_jami — Tue, 05 Feb 2019 21:37:54 GMT

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:

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

and generate/ populate a field value pfl_premium for:

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

pfl_premium: premium * .70

livecount >50

pfl_premium: premium * .80

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

Re: Nested if condition in load script

jonathandienst — Wed, 06 Feb 2019 05:58:03 GMT

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.

Re: Nested if condition in load script

mja_jami — Wed, 06 Feb 2019 14:39:39 GMT

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

Re: Nested if condition in load script

mja_jami — Fri, 08 Feb 2019 17:34:25 GMT

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

Re: Nested if condition in load script

jonathandienst — Wed, 13 Feb 2019 07:05:02 GMT

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,

Re: Nested if condition in load script

mja_jami — Wed, 13 Feb 2019 17:27:59 GMT

Thank you very much!

Re: Nested if condition in load script

mja_jami — Fri, 22 Mar 2019 15:32:39 GMT

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.