2 Replies Latest reply: Jul 28, 2014 4:16 PM by Massimo Grossi RSS

    Eliminate similar rows

    Matt Maino

      I have a scenario where there are multiple rows pertaining to the same account due to user input error. I am wondering if it is possible to only keep one of these rows for each account. Obviously the distinct function in qlikview seems like a solution to this, the only problem is that these similar rows differ slightly by 1 or 2 fields - so distinct doesn't work. I don't really care which of the rows stay, I just want to be able to identify which rows share [Field1] and [Field2] and if there is more than one to only keep one. Thank you.

        • Re: Eliminate similar rows
          Massimo Grossi

          if I understand your req

           

          source:

                          // add an id

          load *, rowno() as id;

                         // your 2 fields

          load * inline [

          field1, field2

          1,a

          1,b

          1,c

          2,a

          2,b

          1,a

          1,a

          2,a

          3,a

          3,b

          ];

           

                         // group by and join to keep only the max (or min) by field1 and field2

          right join (source)

          load field1, field2, max(id) as id

          Resident source

          group by field1,field2;

            • Re: Eliminate similar rows
              Massimo Grossi

              or (it seems easier)

               

              source:

              load * inline [

              field1, field2, f3, f4

              0,a,1,1

              0,b,1,1

              1,a, 10, 10

              1,b, 20, 20

              1,c,60,60

              2,a, 10, 10

              2,b,50,50

              1,a, 25, 25

              1,a, 10, 10

              2,a, 22, 22

              3,a, 21, 21

              3,b, 20, 20

              ];

               

              NoConcatenate load *

              Resident source

              where field1<> Peek(field1) or field2<>Peek(field2)

              order by field1, field2;

               

              DROP Table source;