Reverse Duplicates

[#1]

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 1	COL2
A	B
A	C
A	D
B	A
C	A
D	A

[kushalthakral]

Dear Gokul

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

Regards

Kushal T

[#3]

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 · Accepted solution]

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]

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;

[#6]

Thanks a lot. This solved it.