How to pick required data if certain fields are repeated

[pra_kale]

Hi,

I have a dataset in which policies are repeated because of remark. Below given is the e.g.

Policy_No	Name	Remark
123456	Rajesh	Yes
23456	Nitesh	No
123456	Rajesh	No
56789	Jiten	Yes
123456	Rajesh	No
23456	Nitesh	Yes
366999	Jiire	No

Requirement is where ever policies are repeated and if Remark is Yes then that policy should get selected and for rest policies which are not repeated should get picked up as is.

Final out-put should be like given below.

Policy_No	Name	Remark
23456	Nitesh	Yes
56789	Jiten	Yes
123456	Rajesh	Yes
366999	Jiire	No

Please help to get the desired out-put.

Thanks in Advance.

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD * INLINE [

    Policy_No, Name, Remark

    123456, Rajesh, Yes

    23456, Nitesh, No

    123456, Rajesh, No

    56789, Jiten, Yes

    123456, Rajesh, No

    23456, Nitesh, Yes

    366999, Jiire, No

];

Right Join (Table)

LOAD Policy_No,

  MaxString(Remark) as Remark

Resident Table

Group By Policy_No;

[thomaslg_wq]

Or this

Table:

LOAD DISTINCT

     Policy_No,

     Name,

     Remark

from Source

where Remark='yes';

concatenate(Table)

LOAD DISTINCT

     Policy_No,

     Name,

     Remark

from Source

where not exists(Policy_No,Policy_No);

[pra_kale]

Thanks Sunny It is working .

[pra_kale]

Thanks Thomas...This solution is also working.