Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Script Issue

Dear Friends,

I have follwing data in my QV document

POLICY_NO             POL_TYPE SUM_INSURED

1M0018TC0000519    N                    3100000

1M0018TC0000519    F

1M0016TC0000488    R                    10000000

1M0016TC0000488    N                    10000000

1M0016HH0000221    R                     9374750

1M0016HH0000221    N                     9374750

1M0016HH0000221    F

1M0012TC0000273     R                    1500000

1M0012TC0000273    F

I want tp populate missing SUM_INSURED with following load script. But It does not give the result and mission Sum Insured remain blank. Missing date relates only to POL_TYPE=F  . Kindly help

LOAD

       POLICY_NO,

        IF(PEEK(POLICY_NO)=POLICY_NO AND POL_SUM_INSURED=0,PEEK(POL_SUM_INSURED)*-1,POL_SUM_INSURED)AS SUM_INSURED,

         POL_TYPE

  

Resident HHTC

ORDER BY POLICY_NO,POL_TYPE DESC;

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Pfa

Best Regards,
KC

View solution in original post

4 Replies
upaliwije
Creator II
Creator II
Author

This is my sample file pls help

jyothish8807
Master II
Master II

Try this:

AA:

LOAD * Inline

[

POLICY_NO ,POL_TYPE, SUM_INSURED

1M0018TC0000519,    N, 3100000

1M0018TC0000519, F, 0

1M0016TC0000488, R, 10000000

1M0016TC0000488, N, 10000000

1M0016HH0000221, R, 9374750

1M0016HH0000221, N, 9374750

1M0016HH0000221, F, 0

1M0012TC0000273, R, 1500000

1M0012TC0000273, F, 0

];

NoConcatenate

BB1:

LOAD

POLICY_NO,

POL_TYPE,

IF(POLICY_NO= previous(POLICY_NO) ,peek(SUM_INSURED),SUM_INSURED)AS SUM_INSURED

Resident AA

ORDER BY POLICY_NO;

DROP Table AA;

Hope this is what you are trying to acheive.

Br,

KC

Best Regards,
KC
jyothish8807
Master II
Master II

Pfa

Best Regards,
KC
PrashantSangle

add sum_insurance=0 condition too.

IF(isnull(SUM_INSURED) and SUM_INSURED=0 and POLICY_NO= previous(POLICY_NO) ,peek(SUM_INSURED),SUM_INSURED)AS SUM_INSURED


Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂