Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Dear Gokul
Can you please explain little bit more with exact table structure you have.
Regards
Kushal T
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
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;
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;
Thanks a lot. This solved it.