How To Aggregate the value under one

[pra_kale]

Hi,

I have a data in which CHDRNUM, GROUP_NO, Product,STATUS and Premium columns are there. I want to aggregate the total of premium under one CHDRNUM having status IF, only if Group_No is repeated and any one policy is having status IF and only for product code PPT and GST. Thouh Group_No is repeated but non of the policy is having status IF then aggregation is not allowed.

For rest product codes only policy status IF and PU Should be get considered. No Aggregation. So my final out-put should look like this.

CHDRNNUM	GROUP_NO	Product	STATUS	Premium
2345678	803402	PPT	IF	3500
987456	8456123	GST	IF	5500
32456789	8999912	PST	IF	56000
98723	8912346	CNT	IF	300
6978	8912346	CNT	PU	4500

Your help is appreciated.

Thanks in advance.

marcus_sommer‌

stalwar1‌

[sunny_talwar]

Why is 5645333 not showing up? I got this

Using this

A:

LOAD CHDRNNUM,

     GROUP_NO,

     Product,

     STATUS,

     Premium

FROM [Group_No.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

LOAD Only(If(NEW_STATUS = STATUS, CHDRNNUM)) as CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Group By GROUP_NO, Product, NEW_STATUS;

LOAD CHDRNNUM,

     GROUP_NO,

     Product,

     STATUS,

     Premium,

     If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

[nsetty]

GROUP_NO - 8999912 is not repeated, it should not be in the output right?

[pra_kale]

Hi Sunny,

Thanks for your Help..

The 5645333 won't come only because Product code for this CHDRNUM is CNT and other than Product PPT and GST only policy status IF and PU should be get considered with no Aggregation. This CHDRNUM has status SU so wont't come.

Rest out-put is perfectly OK.

Thanks once  again.

[pra_kale]

Hi,

It will come as it has status IF.

Thanks Nagesh for your Help.

[sunny_talwar]

May be try this

A:

LOAD CHDRNNUM,

    GROUP_NO,

    Product,

    STATUS,

    Premium

FROM [Group_No.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

LOAD Only(If(NEW_STATUS = STATUS, CHDRNNUM)) as CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Where Match(NEW_STATUS, 'IF', 'PU')

Group By GROUP_NO, Product, NEW_STATUS;

LOAD CHDRNNUM,

    GROUP_NO,

    Product,

    STATUS,

    Premium,

    If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

[pra_kale]

Hi Sunny,

Thanks for your Help...

Now the thing happened is Grouping is also get's done for all products.

Only if Group_No is repeated and any one policy is having IF status and only for product code PPT and GST then only aggregation is allowed. Though Group_No is repeated but non of the policy is having status IF then aggregation is not allowed.

For rest product codes only policy status IF and PU Should be get considered with No Aggregation.

CHDRNNUM	GROUP_NO	Premium	Product	STATUS
987456	8456123	5500	GST	IF
2345678	803402	3500	PPT	IF
32456789	8999912	56000	PST	IF
	8912346	1065	CNT	IF
	8912346	4927	CNT	PU

Final Out-put should be like this

CHDRNNUM	GROUP_NO	Premium	Product	STATUS
987456	8456123	5500	GST	IF
2345678	803402	3500	PPT	IF
32456789	8999912	56000	PST	IF
98723	8912346	300	CNT	IF
6978	8912346	4500	CNT	PU
56789	8912346	100	CNT	IF
4789	8912346	121	CNT	IF
65478	8912346	223	CNT	IF
78956	8912346	321	CNT	IF
321	8912346	251	CNT	PU
2221	8912346	97	CNT	PU
4555	8912346	79	CNT	PU

[sunny_talwar · Accepted solution]

Try this

A:

LOAD CHDRNNUM,

    GROUP_NO,

    Product,

    STATUS,

    Premium

FROM [Group_No (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, Only(If(STATUS = 'IF', CHDRNNUM))) as NEW_CHDRNNUM,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

NoConcatenate

LOAD NEW_CHDRNNUM AS CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Where Match(NEW_STATUS, 'IF', 'PU')

Group By NEW_CHDRNNUM, GROUP_NO, Product, NEW_STATUS;

LOAD If(Len(Trim(NEW_CHDRNNUM)) > 0, NEW_CHDRNNUM, CHDRNNUM) as NEW_CHDRNNUM,

CHDRNNUM,

    GROUP_NO,

    Product,

    STATUS,

    Premium,

    If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

[pra_kale]

Hi Sunny,

As  always you have solution for All...

Thanks for your Help....

Just from understanding point of view of code you have written...pl correct me if i am wrong..

1) In first step by Right joining..you have loaded only Group number which have repeated and at least 1 have status IF that you have loaded..but when i put exist statement after this still I got all the policies except only CHDRNUM having status SU get's excluded..

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

2) In Final A Table after Group By you have not put any join then how my condition is get's satisfy "For rest product codes only policy status IF and PU Should be get considered with No Aggregation"  . In final table I am not seeing any new columns which you have created NEW_CHDRNUM and NEW_STATUS, how it happened.

Can you pl help me to understand the code...

Thanks Again for Help...

[sunny_talwar]

1) I will have to take a look at this... may be will check during weekday if you remind me again

2) Creating a new table instead of joining back and renaming NEW_s to the old names...

Also, please mark the appropriate response as the correct response instead of marking your response as correct response. This is not going to be helpful for future visitors

Best,

Sunny