Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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.