Remove Duplicate rows

[Sharbel]

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

[jonashertz]

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;

[BrunPierre · Accepted solution]

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;

[Sharbel · Accepted solution]

Works Perfectly, Many thanks