Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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

Labels (2)
2 Replies
Highlighted
Specialist III
Specialist III

ur loading data from QVD or database

if you loading from database try to use db functions

Channa
Highlighted
Contributor III
Contributor III

Hi,

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

Jami