Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
This is my sample file pls help
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
Pfa
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,