1 Reply Latest reply: Mar 14, 2016 10:52 AM by Sunny Talwar RSS

    Excluding Rows

    Dawn Surguy

      I am trying to find a way to exclude some rows. 

       

      If I have a duplicate in one column, I need to identify a specific name in another column and exclude the remaining rows.

       

      Example 1:

       

      ColumnA          ColumnB

      EmailA              ProductA

      EmailA              ProductB

      EmailA              ProductC

       

      Column A has the same email address but in column B it is for different products.  I am specifically looking for ProductA and would need to exclude the rows for ProductB and ProductC.

       

      Final results:

      ColumnA          ColumnB

      EmailA              ProductA

       

      However, there will be situations where I have the same email address multiple times where the productA will not exist.  I will need to keep all of those rows.

       

      Example 2:

       

      ColumnA          ColumnB

      EmailA              ProductD

      EmailA              ProductB

      EmailA              ProductC

       

      Final results:

      ColumnA          ColumnB

      EmailA              ProductD

      EmailA              ProductB

      EmailA              ProductC

       

      Is this possible to do in QV?

       

      Thanks!

        • Re: Excluding Rows
          Sunny Talwar

          May be something like this:

           

          Table:

          LOAD * Inline [

          ColumnA, ColumnB

          EmailA,    ProductA

          EmailA,    ProductB

          EmailA,    ProductC

          EmailB,    ProductD

          EmailB,    ProductB

          EmailB,    ProductC

          ];

           

          Left Join (Table)

          LOAD ColumnA,

            ColumnB as NewColumnB

          Resident Table

          Where ColumnB = 'ProductA';

           

          FinalTable:

          LOAD DISTINCT *;

          LOAD ColumnA,

            If(Len(Trim(NewColumnB)) > 0, NewColumnB, ColumnB) as ColumnB

          Resident Table;