topic Re: Data grouing in QlikView

Data grouing

upaliwije — Sun, 05 Oct 2014 11:28:03 GMT

Hi Friends

I have following data relating to a Insurance Policies. Here I have 4 POL_TYPES

New,Ren,Add,Ref

Add and Ref results from New or Ren Policies.I want to obtain a report in the following format showing only New and Ren Policies. Add and Ref should be added to either New or Ren as the case be Eg: Add in respect of New policies should be added to New Pol Type. The fields should be Policy No and Pol_period To

Pls help

Policy No Pol_Type Premium

New

Ren

POLICY_NO	POL_PERIOD_FROM	POL_PERIOD_TO	POL_TYPE	PREMIUM
31A0000367	11-Apr-2013	10-Apr-2014	NEW	287,415
31A0000367	11-Apr-2013	10-Apr-2014	ADD	127,600
31A0000367	11-Apr-2013	10-Apr-2014	REF	-23,420
11A0000272	20-Dec-2012	19-Dec-2013	REN	36,784
21A0000340	30-Nov-2012	29-Nov-2013	REN	91,718
21A0000340	30-Nov-2012	29-Nov-2013	REF	-14,933
21A0000341	30-Nov-2012	29-Nov-2013	NEW	54,922
21A0000341	30-Nov-2012	29-Nov-2013	REF	-13,910
101A000146	18-Mar-2013	17-Mar-2014	REN	58,094
AL1A000052	06-Feb-2013	05-Feb-2014	REN	41,282
21B0000723	23-Nov-2012	22-Nov-2013	NEW	118,028
31A0000361	25-Mar-2013	24-Mar-2014	NEW	22,688
111A000260	28-Oct-2012	27-Oct-2013	REN	51,776
21A0000331	11-Oct-2012	10-Oct-2013	NEW	34,168
41A0000464	28-Feb-2014	26-Jul-2014	NEW	51,165
31B0000748	13-Mar-2013	12-Mar-2014	NEW	66,185
31B0000748	13-Mar-2013	12-Mar-2014	REF	-11,605
21A0000334	26-Oct-2012	25-Oct-2013	NEW	151,976
21A0000334	26-Oct-2012	25-Oct-2013	ADD	126,808
21A0000334	26-Oct-2012	25-Oct-2013	REF	-146,305
061A000713	03-Apr-2013	02-Apr-2014	REF	-3,334
061A000713	03-Apr-2013	02-Apr-2014	REN	22,538

Re: Data grouing

anbu1984 — Sun, 05 Oct 2014 11:44:58 GMT

Load POLICY_NO,POL_TYPE,Sum(PREMIUM) As PREMIUM Group by POLICY_NO,POL_PERIOD_FROM,POL_TYPE;

Load POLICY_NO,POL_PERIOD_FROM,POL_PERIOD_TO,If(POL_TYPE='ADD','NEW',If(POL_TYPE='REF','REN',POL_TYPE)) AS POL_TYPE,PREMIUM Inline [

POLICY_NO POL_PERIOD_FROM POL_PERIOD_TO POL_TYPE PREMIUM

31A0000367 11-Apr-2013 10-Apr-2014 NEW 287,415

31A0000367 11-Apr-2013 10-Apr-2014 ADD 127,600

31A0000367 11-Apr-2013 10-Apr-2014 REF -23,420

11A0000272 20-Dec-2012 19-Dec-2013 REN 36,784

21A0000340 30-Nov-2012 29-Nov-2013 REN 91,718

21A0000340 30-Nov-2012 29-Nov-2013 REF -14,933

21A0000341 30-Nov-2012 29-Nov-2013 NEW 54,922

21A0000341 30-Nov-2012 29-Nov-2013 REF -13,910

101A000146 18-Mar-2013 17-Mar-2014 REN 58,094

AL1A000052 06-Feb-2013 05-Feb-2014 REN 41,282

21B0000723 23-Nov-2012 22-Nov-2013 NEW 118,028

31A0000361 25-Mar-2013 24-Mar-2014 NEW 22,688

111A000260 28-Oct-2012 27-Oct-2013 REN 51,776

21A0000331 11-Oct-2012 10-Oct-2013 NEW 34,168

41A0000464 28-Feb-2014 26-Jul-2014 NEW 51,165

31B0000748 13-Mar-2013 12-Mar-2014 NEW 66,185

31B0000748 13-Mar-2013 12-Mar-2014 REF -11,605

21A0000334 26-Oct-2012 25-Oct-2013 NEW 151,976

21A0000334 26-Oct-2012 25-Oct-2013 ADD 126,808

21A0000334 26-Oct-2012 25-Oct-2013 REF -146,305

061A000713 03-Apr-2013 02-Apr-2014 REF -3,334

061A000713 03-Apr-2013 02-Apr-2014 REN 22,538] (delimiter is '\t') ;

Re: Data grouing

upaliwije — Sun, 05 Oct 2014 11:51:27 GMT

Thanks

According you syntax Add is always treated as New and Ref is Ren. But that is not the case Add or Ref could be New or Ren based on the policy No on the

Re: Data grouing

anbu1984 — Sun, 05 Oct 2014 12:10:32 GMT

Temp:

Load * Inline [

POLICY_NO POL_PERIOD_FROM POL_PERIOD_TO POL_TYPE PREMIUM

31A0000367 11-Apr-2013 10-Apr-2014 NEW 287,415

31A0000367 11-Apr-2013 10-Apr-2014 ADD 127,600

31A0000367 11-Apr-2013 10-Apr-2014 REF -23,420

11A0000272 20-Dec-2012 19-Dec-2013 REN 36,784

21A0000340 30-Nov-2012 29-Nov-2013 REN 91,718

21A0000340 30-Nov-2012 29-Nov-2013 REF -14,933

21A0000341 30-Nov-2012 29-Nov-2013 NEW 54,922

21A0000341 30-Nov-2012 29-Nov-2013 REF -13,910

101A000146 18-Mar-2013 17-Mar-2014 REN 58,094

AL1A000052 06-Feb-2013 05-Feb-2014 REN 41,282

21B0000723 23-Nov-2012 22-Nov-2013 NEW 118,028

31A0000361 25-Mar-2013 24-Mar-2014 NEW 22,688

111A000260 28-Oct-2012 27-Oct-2013 REN 51,776

21A0000331 11-Oct-2012 10-Oct-2013 NEW 34,168

41A0000464 28-Feb-2014 26-Jul-2014 NEW 51,165

31B0000748 13-Mar-2013 12-Mar-2014 NEW 66,185

31B0000748 13-Mar-2013 12-Mar-2014 REF -11,605

21A0000334 26-Oct-2012 25-Oct-2013 NEW 151,976

21A0000334 26-Oct-2012 25-Oct-2013 ADD 126,808

21A0000334 26-Oct-2012 25-Oct-2013 REF -146,305

061A000713 03-Apr-2013 02-Apr-2014 REF -3,334

061A000713 03-Apr-2013 02-Apr-2014 REN 22,538] (delimiter is '\t') ;

Left Join

Load POLICY_NO,POL_PERIOD_TO,If(Sum(POL_TYPE='NEW'),'NEW','REN') As POL_TYPE_New Resident Temp Group by POLICY_NO,POL_PERIOD_TO;

Final:

Load POLICY_NO,POL_TYPE_New,Sum(PREMIUM) As PREMIUM Resident Temp Group By POLICY_NO,POL_TYPE_New;

Drop table Temp;

Re: Data grouing

upaliwije — Sun, 05 Oct 2014 12:31:41 GMT

Thanks for your help

This is not what I meant

Policy No Pol_type Premium

1 NEW 1000

1 ADD 200

2 REN 1500

2 REF -500

3 NEW 2000

3 REF -2000

Report

Policy No Pol_type Premium

1 NEW 1200 (1000+200)

2 REN 1000 (1500-500)

3 NEW 0 (2000-2000)

I want the above report pls

Re: Re: Data grouing

anbu1984 — Sun, 05 Oct 2014 13:01:13 GMT

Is this your expected result?

Re: Data grouing

upaliwije — Sun, 05 Oct 2014 13:09:06 GMT

Yes

That is exactly what I want Pls

Re: Re: Data grouing

anbu1984 — Sun, 05 Oct 2014 13:22:34 GMT

Above script gives your expected result. But delimiter is changed to spaces when I posted script above.

I mentioned delimiter as "(delimiter is '\t')" in script. Make sure you have tab delimiter in the data.

Check attached app

Re: Data grouing

upaliwije — Sun, 05 Oct 2014 13:42:27 GMT

Thanks for all the help given