Show duplicate value within one field

[Woojtek_96]

Hi all,

I have a question, how to check if I have duplicate value within one field.

For example:

Raport_table_tmp:
load
Raport_ResNo,
Raport_PersonOnResNo,
Raport_ServiceCode,
Resident ResTable

and i want to check if particular Person on reservation has duplicates in Raport_ServiceCode value, if yes load them if not make a null().

Can i do it with script? I dont want to make it in front with set analysis becouse of many rows of table

Testing scenario

ResNo	PersonOnRes	ServiceCode
111	1	Service1
111	1	Service1
111	2	Service1
111	2	Service2
111	3	Service 1

and i want to load only person who has duplicated service code (in case this is person number one) and concat those services into one row in column (on front)

[edwin · Accepted solution]

you can aggregate it something like:

tmp: load PersonRes, ServiceCode, count(ServiceCode) as cnt resident DATA group by PersonRes, ServiceCOde;

nonconcatenate NewData: load PersonRes, ServiceCode resident tmp where cnt>1;  drop table tmp

this last table gives you the Person,ServiceCode that are multiple.  you can left join or inner join it to your original table depending on what you need.