Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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;

View solution in original post

5 Replies
kushalthakral
Contributor III

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;

View solution in original post

Highlighted
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.