Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How can I remove dupes from a table?

Hello,

Here's another issue I am facing. Thanks in advance for your help:

I load this table:

LEISURE:

Load * inline

[LOCATION,  TYPE,        GROUP,

PARIS,          CITY,          CULTURE

PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

ENGLAND,   COUNTRY, MUSIC

];

I obtain:

How can I transform that table into a smaller table, discarding the 2nd occurrence with PARIS, CITY with GROUP MUSIC and only keeping the 1st occurrence PARIS, CITY with GROUP=CULTURE.

I would like to have this LEISURE table at the end:

Many thanks. Annick

1 Solution

Accepted Solutions
MVP
MVP

Re: How can I remove dupes from a table?

If the OP is interested in the first occurence, I would then rather use

LEISURE:

LOAD LOCATION,

          TYPE,

          FirstValue(GROUP) as GROUP

Group By LOCATION, TYPE;

Load * inline

[LOCATION,  TYPE,        GROUP,

PARIS,          CITY,          CULTURE

PARIS,          CITY,          MUSIC                    /* [PARIS, CITY] is a DUPE, I would like to discard it

ENGLAND,  COUNTRY, MUSIC

];

3 Replies
MVP
MVP

Re: How can I remove dupes from a table?

Maybe like

LEISURE:

Load *, LOCATION & TYPE as DupeKey inline

[LOCATION,  TYPE,        GROUP,

PARIS,          CITY,          CULTURE

PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

ENGLAND,   COUNTRY, MUSIC

] WHERE NO EXISTS(DupeKey, LOCATION & TYPE);

DROP FIELD DupeKey;

MVP
MVP

Re: How can I remove dupes from a table?

May be like this:

LEISURE:

LOAD LOCATION,

          TYPE,

          MinString(GROUP) as GROUP

Group By LOCATION, TYPE;

Load * inline

[LOCATION,  TYPE,        GROUP,

PARIS,          CITY,          CULTURE

PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

ENGLAND,   COUNTRY, MUSIC

];

MVP
MVP

Re: How can I remove dupes from a table?

If the OP is interested in the first occurence, I would then rather use

LEISURE:

LOAD LOCATION,

          TYPE,

          FirstValue(GROUP) as GROUP

Group By LOCATION, TYPE;

Load * inline

[LOCATION,  TYPE,        GROUP,

PARIS,          CITY,          CULTURE

PARIS,          CITY,          MUSIC                    /* [PARIS, CITY] is a DUPE, I would like to discard it

ENGLAND,  COUNTRY, MUSIC

];