1 Reply Latest reply: Dec 9, 2014 10:36 PM by jagan mohan rao appala RSS

    Reverse Duplicates II Expressions

      How do we remove reverse duplicates in Qlikview?

       

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

      COL 1COL2
      AB
      AC
      AD
      BA
      CA
      DA

       

      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 mohan rao appala

          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.