Removing the similar rows based on the error in one row

[Anonymous]

Hi Guys,

I'm facing a difficulty while conditionally removing the data from the dataset. Please see the scenario below

Product ID     Product     Selling Price

ABC              Grocery     12345

ABC              Grocery-1    -

xyz             non-veg food  23211

So this is a dummy dataset where we are fetching selling proce based on the product name. So in the second row, there is no selling price for "Grocery-1", therefore based on this condition I want to remove both the rows where product ID = ABC. But when I'm trying to do this, QV is only removing the row where where price is null.

Thanks in advance for your help!!!

[Anonymous]

Product ID     Product          Selling Price

ABC              Grocery          12345

ABC              Grocery-1         -

xyz             non-veg food       23211

xyz           Non-veg food-1     2232

pqr          Veg Food               1121

def          Alcohol                    1234

pqr           veg Food-1            -

So here basically both ABC and pqr product ID need to be removed for all the products. Hope it clarifies the situation

[Anonymous · Accepted solution]

Hi,

You can create temp table with product id where selling price is null and use the "Not Exists" condition in your main table to remove those products. Something like below,

Temp:

Load

     [Product ID] as [Product ID Remove]

Resident <Table> Where Len([Selling Price])=0;

Order:

Load

     [Product ID],

     Product,

     [Selling Price]

From <Table> Where Not Exists([Product ID Remove], [Product ID]);

Hope it helps you

[Anonymous]

Works like a charm....thanks so much!!!