topic Re: Identify Duplicated values in Water Cooler

Identify Duplicated values

johngouws — Sun, 09 Jun 2024 04:47:42 GMT

Good Day.

I am looking for any advise on how to resolve this issue. I have a dataset consisting of Groups, with Sites and Site Names. From time to time a new site can accidently be allocated the same Site Code or Site Name as an existing site.

I am looking for a way to identify duplicate Site Codes and Site Names, as per the included PNG file. It can also be that the Site Codes are different and the Site Names are the same.

The “# Sites” expression is “RowNo(Total) ” and currently the expression to Identify the duplicate Site code is “Rangecount(Above(Count(Sitecode), 0, RowNo()))”.

I would be appreciate any suggestions on the issue, whether in a straight table or by creating a separate table.

Thank you – much appreciated.

Re: Identify Duplicated values

rubenmarin — Sun, 09 Jun 2024 06:38:56 GMT

Hi, you can add an expression with just Count(SiteCode), and sorted descendant by this expression will set the duplicated codes in the first rows, and/or add a red color background "If(Count(SiteCode)>1, LightRed())"

To do the check by group the count could be Count(Group &'_'& SiteCode).

The same can be done by SiteName.

Re: Identify Duplicated values

TauseefKhan — Sun, 09 Jun 2024 06:51:32 GMT

You can use a combination of the Count() function and a Group By clause in your script.

Create a new table where you'll flag the duplicates.

// Load your initial data
SitesTable:
LOAD
Group,
SiteCode,
Company
FROM [YourDataSource];

// Create a table to flag duplicate Site Codes
DuplicateSiteCodes:
LOAD
SiteCode,
If(Count(SiteCode) > 1, 'Duplicate', 'Unique') as DuplicateCodeFlag
RESIDENT SitesTable
GROUP BY SiteCode;

// Create a table to flag duplicate Site Names
DuplicateCompany:
LOAD
Company,
If(Count(Company) > 1, 'Duplicate', 'Unique') as DuplicateNameFlag
RESIDENT SitesTable
GROUP BY Company;

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

Re: Identify Duplicated values

Vegar — Sun, 09 Jun 2024 08:21:33 GMT

If you want to do this in the script you can use exists() to identify duplicates.

Load

Sitecode, Company, Group,

Exists(Sitecode) as duplicate_site

From Source;

You could also try to use the window() function that was released in September 2023. Here is two alternatives depending on what you want to count.

Load

Window(count(Sitecode),Sitecode) as no_of_sitecodes,

Window(count( distinct Company),Sitecode) as no_of_unique_companies_per_sitecode,

;

Load

Sitecode, Company, Group

From Source;