topic Re: Script Issue in QlikView

Script Issue

upaliwije — Fri, 22 Jun 2018 05:08:12 GMT

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;

Re: Script Issue

upaliwije — Fri, 22 Jun 2018 06:14:19 GMT

This is my sample file pls help

Re: Script Issue

jyothish8807 — Fri, 22 Jun 2018 06:34:52 GMT

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,

Re: Script Issue

jyothish8807 — Fri, 22 Jun 2018 06:36:46 GMT

Pfa

Re: Script Issue

PrashantSangle — Fri, 22 Jun 2018 07:44:18 GMT

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,