Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Reverse Duplicates

How do we remove reverse duplicates in Qlikview?

I have a table that contains rows like AB,AC,AD and BA,BC,BD. I want to retain only the first three rows? How to do this in Qlikview using self join?

COL 1COL2
AB
AC
AD
BA
CA
DA
1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Reverse Duplicates

The following script works and does what want to do:

Table1:

LOAD *,Ord(Pos1)+Ord(Pos2) as Value;

LOAD * Inline [

Pos1,Pos2

A,B

A,C

A,D

B,A

C,A

D,A

];

Final:

NoConcatenate

LOAD Value, FirstValue(Pos1) as Pos1,FirstValue(Pos2) as Pos2

Resident Table1 Group By Value;

Drop Field Value From Final;

DROP Table Table1;

5 Replies
kushalthakral
Contributor II

Re: Reverse Duplicates

Dear Gokul

Can you please explain little bit more with exact table structure  you have.

Regards

Kushal T

Not applicable

Re: Reverse Duplicates

Dear Kushal,

The following is a description and solution for the problem in SQL. I am just not able to figure out how to do this in QlikView?

http://stackoverflow.com/questions/13041802/remove-reverse-duplicates-from-an-sql-query

Regards,

Gokul

nagaiank
Valued Contributor III

Re: Reverse Duplicates

The following script works and does what want to do:

Table1:

LOAD *,Ord(Pos1)+Ord(Pos2) as Value;

LOAD * Inline [

Pos1,Pos2

A,B

A,C

A,D

B,A

C,A

D,A

];

Final:

NoConcatenate

LOAD Value, FirstValue(Pos1) as Pos1,FirstValue(Pos2) as Pos2

Resident Table1 Group By Value;

Drop Field Value From Final;

DROP Table Table1;

anbu1984
Honored Contributor III

Re: Reverse Duplicates

Initial:

LOAD *,If(Pos1>Pos2,Pos2&Pos1,Pos1&Pos2) As New  Inline [

Pos1,Pos2

A,B

A,C

A,D

B,A

C,A

D,A

];

NoConcatenate

Final:

Load * Resident Initial Where New <> Previous(New) Order by New,Pos1;

Drop table Initial;

Not applicable

Re: Reverse Duplicates

Thanks a lot. This solved it.

Community Browser