Require only one value if one field is repetaed

[pra_kale]

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.

[vikasmahajan]

What is exact expectations ?   data is correct !

[PradeepReddy]

can u elaborate the requirement?  what is your expected output?

[Digvijay_Singh · Accepted solution]

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;

[pra_kale]

Thanks Digvijay for your Help..

It's working fine.

[Digvijay_Singh]

Glad it worked for you.