Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
The data which I have attached in that I have a policy numbers, Products, Bifurcation and Sum Assured columns.
My requirement is If policy number is repeated having bifurcation as 'Main' then I want only 1 policy number should appear in the final data where Sum Assured is present but i want all the values for same policy where bifurcation is 'Sub_benefit' but if policy number is not repeated though Sum Assured is 0 and having bifurcation as Main then that policy should present in the data..
For e.g. my final data should look like given below...means If policy number is repeated where bifurcation is
Policy_Number | Product | Sum Assured | Bifurcation |
12345678 | CSR | 10000 | Main |
1456231 | CSD | 65000 | Main |
3215600 | PJY | 0 | Main |
356123 | PMY | 0 | Main |
98000012 | ABC | 32000 | Main |
12345678 | XYZ | 12000 | Sub_benefit |
12345678 | ZEO | 23000 | Sub_benefit |
1456231 | WEX | 56000 | Sub_benefit |
I have attempted through Group by but it is not giving proper result.. script is given below..
A:
LOAD Policy_Number,
Product,
[Sum Assured] as SA,
Bifurcation
FROM
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
B:
LOAD
Policy_Number,
Product,
Bifurcation,
sum(SA) as SA_1
Resident A
Group by Policy_Number, Product,Bifurcation;
DROP Table A;
EXIT Script
Please help to close the issue..
Thanks in Advance.
Check this, I changed data a bit for policy 12345678 to create duplicate case with sum assured 0 to verify if it is removed after script run-
T1:
Load * inline [
Policy_Number Product SumAssured Bifurcation
12345678 CSR 10000 Main
1456231 CSD 65000 Main
12345678 PJY 0 Main
356123 PMY 0 Main
98000012 ABC 32000 Main
12345678 XYZ 12000 Sub_benefit
12345678 ZEO 23000 Sub_benefit
1456231 WEX 56000 Sub_benefit ](delimiter is spaces);
NoConcatenate
T2:
Load *
Resident T1
Where Bifurcation='Main';
NoConcatenate
T3:
Load *
Resident T2
Where Previous(Policy_Number)<>Policy_Number
Order By Policy_Number,SumAssured desc;
Concatenate
Load *
Resident T1
Where Bifurcation='Sub_benefit'
Order By Policy_Number;
Drop Table T1,T2;
What is exact expectations ? data is correct !
can u elaborate the requirement? what is your expected output?
Check this, I changed data a bit for policy 12345678 to create duplicate case with sum assured 0 to verify if it is removed after script run-
T1:
Load * inline [
Policy_Number Product SumAssured Bifurcation
12345678 CSR 10000 Main
1456231 CSD 65000 Main
12345678 PJY 0 Main
356123 PMY 0 Main
98000012 ABC 32000 Main
12345678 XYZ 12000 Sub_benefit
12345678 ZEO 23000 Sub_benefit
1456231 WEX 56000 Sub_benefit ](delimiter is spaces);
NoConcatenate
T2:
Load *
Resident T1
Where Bifurcation='Main';
NoConcatenate
T3:
Load *
Resident T2
Where Previous(Policy_Number)<>Policy_Number
Order By Policy_Number,SumAssured desc;
Concatenate
Load *
Resident T1
Where Bifurcation='Sub_benefit'
Order By Policy_Number;
Drop Table T1,T2;
Thanks Digvijay for your Help..
It's working fine.
Glad it worked for you.