Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Extract Duplicate Data to a new Table

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

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Capture.PNG.pngI used an extra hidden 'dummy' expression with this formula to hide cities that appear only once:

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

View solution in original post

2 Replies
JonnyPoole
Employee
Employee

Capture.PNG.pngI used an extra hidden 'dummy' expression with this formula to hide cities that appear only once:

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

maxgro
MVP
MVP

you can add a field to your table

left join (table)

load

City,

count (City) as count

resident

table

group by City;