topic Re: DATA LOAD in QlikView

DATA LOAD

upaliwije — Wed, 09 Nov 2016 05:13:13 GMT

Hi,

In my QV document I have followinf script

sales1:

LOAD

POLICY_NO,

MID(POLICY_NO,5,2)AS POLICY_YEAR,

POL_SUM_INSURED,

POL_PERIOD_TO,

ME_CODE,

TRN_TYPE,

POL_TYPE,

POL_STATUS,

REF_NO,

RC_TRN_TYPE,

DATE(T_DATE,'DD-MM-YY')AS T_DATE , //tranaction date

TRN_DATE, //risk date

CP,

PREMIUM

FROM

d:\Qlikview\QVD\FACT_Table\Sales.qvd

(qvd)

WHERE match(POLICY_NO,'MO00101C001855','MO00111C002019')

Temp:

Load

Where NET_PRE=0;

Load Sum(PREMIUM) as NET_PRE,

POL_PERIOD_TO AS PER_TO,

POLICY_NO as PLNO

Resident sales1 Group By POLICY_NO,POL_PERIOD_TO;

sales:

NoConcatenate

Load

Resident sales1 where Exists (PER_TO,POL_PERIOD_TO)and Exists(PLNO,POLICY_NO) ;

drop table sales1,Temp;

After Loading data I get follwing out put

POLICY_NO	POL_PERIOD_TO	GROSS_PREMIUM	REFUNDS
MO00101C001855	42093	28,605	0
MO00101C001855	42824	45,505	-45,505
MO00111C002019	42093	17,765	-17,765

But in my output report I do not want Top row to apper. I only want records where gross premium is positive amount and exact negative value as refunds to be shown. Can some educate how should I modify the syntax to load donly relevant data

Re: DATA LOAD

robert_mika — Wed, 09 Nov 2016 06:25:47 GMT

Is IREFUNDS expression or Field?

Re: DATA LOAD

upaliwije — Wed, 09 Nov 2016 06:33:23 GMT

An Expression

SUM({$<POL_TYPE={'F'}>}PREMIUM) = REFUNDS

Re: DATA LOAD

upaliwije — Wed, 09 Nov 2016 08:42:53 GMT

Re: DATA LOAD

upaliwije — Wed, 09 Nov 2016 08:58:02 GMT

I have attached my QV document. Pls advise

Re: DATA LOAD

robert_mika — Wed, 09 Nov 2016 10:10:23 GMT

Could you attached the Excel file?

Re: DATA LOAD

upaliwije — Wed, 09 Nov 2016 10:19:12 GMT

Yes of course

Re: DATA LOAD

Wed, 09 Nov 2016 11:03:07 GMT

Hi, I used your data and script i got the expected result for you.

Please look into the attached app. please correct me if do wrong.

Re: DATA LOAD

upaliwije — Wed, 09 Nov 2016 14:11:48 GMT

in your QV document all data is loaded including records that are not required. What I want is to load only recors where poistive vales and negative values are same

Re: DATA LOAD

upaliwije — Thu, 10 Nov 2016 03:10:11 GMT

Can You pls help me