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:
- 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
