Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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

View solution in original post

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.