Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
GROUP_NO - 8999912 is not repeated, it should not be in the output right?
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.
Hi,
It will come as it has status IF.
Thanks Nagesh for your Help.
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;
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 |
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;
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...
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