Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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);
Thanks Sunny It is working .
Thanks Thomas...This solution is also working.