Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
and generate/ populate a field value pfl_premium for:
pfl_premium: premium * .70
pfl_premium: premium * .80
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
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,
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.
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
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
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,
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.