Show duplicate value within one field

Woojtek_96 — Thu, 29 Oct 2020 13:50:04 GMT

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)

Re: Show duplicate value within one field

edwin — Thu, 29 Oct 2020 14:46:14 GMT

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.

topic Show duplicate value within one field in QlikView

Show duplicate value within one field

Re: Show duplicate value within one field