topic Re: Remove Duplicate rows in App Development

Remove Duplicate rows

Sharbel — Mon, 02 Oct 2023 08:06:46 GMT

Hello,

The following table includes data regarding employees (employee number , full name).

As seen in the table, it contains duplicate Emp_no (marked in Yellow):

Any idea on how to remove the duplicate Emp_no so that the desired table would look like:

Regards,

Sharbel

Re: Remove Duplicate rows

jonashertz — Mon, 02 Oct 2023 09:09:07 GMT

Hi,

You can try to use the Autonumber function with two fields. See below code. Comment the where clause to see the unfiltered table and note the Order By to select which order you would like the load to pick the names.

t1:

Load * Inline [

code, name

1, john

2, mary

2, lisa

3, andy

4, fred

5, joe

5, harry

5, sally

];

t2:

Load

AutoNumber(name,code) as Rank,

code,

name

Resident t1

where AutoNumber(name,code) = 1

Order by name desc

;

drop table t1;

Re: Remove Duplicate rows

BrunPierre — Mon, 02 Oct 2023 10:32:14 GMT

Temp:
LOAD * Inline [
Empo_no, Full Name
1000, Emily Johnson
1100, Alexander Taylor
1200, John Smith
1200, Dr. John Smith
1300, Olivia Davis
1400, William Brown
1400, Dr. William Brown
1500, Emma Anderson
1600,Benjamin Wilson
1700,Sophia Martinez
1700,Dr. Sophia Martinez
1800,Mia Harris
1900,Daniel Clark
1900,Dr. Daniel Clark];

NoConcatenate
Data:
LOAD Empo_no,
LastValue("Full Name") as "Full Name"

Resident Temp
Group By Empo_no;

DROP Table Temp;

Re: Remove Duplicate rows

Sharbel — Mon, 02 Oct 2023 11:15:42 GMT

Works Perfectly, Many thanks