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

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
Specialist III
Specialist III

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
Creator III
Creator III

Dear Gokul

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

Regards

Kushal T

Not applicable
Author

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
Specialist III
Specialist III

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
Master III
Master III

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
Author

Thanks a lot. This solved it.