Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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 (2)
Labels (2)
2 Replies
Channa
Valued Contributor III

Re: Nested if condition in load script

ur loading data from QVD or database

if you loading from database try to use db functions

Channa
mja_jami
New Contributor III

Re: Nested if condition in load script

Hi,

I'm loading from QVD. It gives me correct sum for 2018 conditions but not 2019 😞
Regards,

Jami