Extract Duplicate Data to a new Table

[#1]

Hi,

I have received an issue from on of our users who would like to extract duplicate data in a field into another table.  The data is as follows:

LOAD * INLINE [

    Unique_No, City, Cust_Name,

    1, London, Jeff Daniels,

    2, Birmingham, Colin Powell

    3, London, Frank Harrison

    4, Birmingham, Russell Crowe

    5, Aberdeen, Reggie Yates

    6, London, Donald Donaldson

    7, Slough, Rita Sutherland

    8, Reading, Jamie Curtis

    9, Birmingham, Lois Griffin

    10, Liverpool, Megan Clarke

];

Basically what we are looking for, the column city had the duplicate, is there a way we can get the data out so one table had all the data above, but another table only has the duplicate data (Which just happens to be Birmingham and London)

Table should show as

Unique_no      City                Cust_Name

1                     London           Jeff Daniles

2                     Birmingham    Colin Powell

3                     London           Frank Harrison

4                     Birmingham    Russell Crowe

6                     London           Donald Donaldson

9                     Birmingham    Lois Griffin

Regards,

Jon

[JonnyPoole · Accepted solution]

I used an extra hidden 'dummy' expression with this formula to hide cities that appear only once:

if( count( total <City> City) > 1, 1)

[maxgro]

you can add a field to your table

left join (table)

load

City,

count (City) as count

resident

table

group by City;