Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Employee
Employee

Re: Extract Duplicate Data to a new Table

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)

2 Replies
Employee
Employee

Re: Extract Duplicate Data to a new Table

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)

MVP
MVP

Re: Extract Duplicate Data to a new Table

you can add a field to your table

left join (table)

load

City,

count (City) as count

resident

table

group by City;

Community Browser