topic Re: Reverse Duplicates in QlikView

Reverse Duplicates

Mon, 08 Dec 2014 03:03:52 GMT

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

Re: Reverse Duplicates

kushalthakral — Mon, 08 Dec 2014 03:31:51 GMT

Dear Gokul

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

Regards

Kushal T

Re: Reverse Duplicates

Mon, 08 Dec 2014 03:47:33 GMT

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

Re: Reverse Duplicates

nagaiank — Mon, 08 Dec 2014 04:02:43 GMT

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;

Re: Reverse Duplicates

anbu1984 — Mon, 08 Dec 2014 05:41:51 GMT

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;

Re: Reverse Duplicates

Tue, 09 Dec 2014 03:06:42 GMT

Thanks a lot. This solved it.