Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II
‎2014-10-05 07:28 AM

Data grouing

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_NOPOL_PERIOD_FROMPOL_PERIOD_TOPOL_TYPEPREMIUM
31A000036711-Apr-201310-Apr-2014NEW287,415
31A000036711-Apr-201310-Apr-2014ADD127,600
31A000036711-Apr-201310-Apr-2014REF-23,420
11A000027220-Dec-201219-Dec-2013REN36,784
21A000034030-Nov-201229-Nov-2013REN91,718
21A000034030-Nov-201229-Nov-2013REF-14,933
21A000034130-Nov-201229-Nov-2013NEW54,922
21A000034130-Nov-201229-Nov-2013REF-13,910
101A00014618-Mar-201317-Mar-2014REN58,094
AL1A00005206-Feb-201305-Feb-2014REN41,282
21B000072323-Nov-201222-Nov-2013NEW118,028
31A000036125-Mar-201324-Mar-2014NEW22,688
111A00026028-Oct-201227-Oct-2013REN51,776
21A000033111-Oct-201210-Oct-2013NEW34,168
41A000046428-Feb-201426-Jul-2014NEW51,165
31B000074813-Mar-201312-Mar-2014NEW66,185
31B000074813-Mar-201312-Mar-2014REF-11,605
21A000033426-Oct-201225-Oct-2013NEW151,976
21A000033426-Oct-201225-Oct-2013ADD126,808
21A000033426-Oct-201225-Oct-2013REF-146,305
061A00071303-Apr-201302-Apr-2014REF-3,334
061A00071303-Apr-201302-Apr-2014REN22,538
614 Views
0 Likes
1 Solution

Accepted Solutions
anbu1984
Master III
Master III
‎2014-10-05 09:22 AM

In response to upaliwije

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

View solution in original post

qv_comm_136464qvw.qvw
504 Views
0 Likes
8 Replies
anbu1984
Master III
Master III
‎2014-10-05 07:44 AM

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') ;

504 Views
0 Likes
upaliwije
Creator II
Creator II
‎2014-10-05 07:51 AM
Author

In response to anbu1984

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

504 Views
0 Likes
anbu1984
Master III
Master III
‎2014-10-05 08:10 AM

In response to upaliwije

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;

504 Views
0 Likes
upaliwije
Creator II
Creator II
‎2014-10-05 08:31 AM
Author

In response to anbu1984

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

504 Views
0 Likes
anbu1984
Master III
Master III
‎2014-10-05 09:01 AM

In response to upaliwije

Is this your expected result?

976 KB
504 Views
0 Likes
upaliwije
Creator II
Creator II
‎2014-10-05 09:09 AM
Author

In response to anbu1984

Yes

That is exactly what I want Pls

504 Views
0 Likes
anbu1984
Master III
Master III
‎2014-10-05 09:22 AM

In response to upaliwije

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

qv_comm_136464qvw.qvw
505 Views
0 Likes
upaliwije
Creator II
Creator II
‎2014-10-05 09:42 AM
Author

In response to anbu1984

Thanks for all the help given

504 Views
0 Likes