topic Reverse Duplicates II Expressions in QlikView

Reverse Duplicates II Expressions

Tue, 09 Dec 2014 08:14:09 GMT

How do we remove reverse duplicates in Qlikview?

I have a table that contains rows like AB,AC,AD and BA,BC,BD.

COL 1	COL2
A	B
A	C
A	D
B	A
C	A
D	A

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

The problem has already been solved using scripting. Reverse Duplicates

My Question is, could we achieve the same using QlikView chart expressions?

Regards,

Gokul

Re: Reverse Duplicates II Expressions

jagan — Wed, 10 Dec 2014 03:36:33 GMT

Hi Gokul,

Handling this in set analysis is very difficult what I can suggest is arrive a flag in script and then use set analysis to exclude the duplicates, try like below

Temp:

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

LOAD * Inline [

Pos1,Pos2

A,B

A,C

A,D

B,A

C,A

D,A

];

Data:

LOAD

If(Peek(Value) <> Value, 0, 1) AS IsDuplicate

RESIDENT Temp

ORDER BY Value;

DROP TABLE Temp;

Now in chart try below expressions

Count({<IsDuplicate={0}>} POS1)

Hope this helps you.

Regards,

Jagan.