Qlikview how to delete everything in one table when certain values exist in other tables

[tonytang]

Hi,

I am very new to Qlikview. Please see below picture. Could anyone tell me how i can delete all records in Transaction table when Product Name='ABC' in Product Table in data edit script? I wanna reduce the total numbers of data loaded to the database to speed up the computation.

I have spent two days on trying to resolve it, but end up no solution can be found.  Hope there is someone willing to help me. 

[rwunderlich · Accepted solution]

There are several approaches depending on the order in which you load the tables. Here's a solution that would work if you filter the tables as you load then the first time. The idea is that you start by not loading "ABC" in the product table and then use the "exists" function to filter the other tables as you load. Something like:

Product:
LOAD *
From .... 
Where [Product Name] <> 'ABC';

Order:
LOAD *
From ...
Where exists([Product Sub-Category]);

Transaction:
LOAD * 
From ...
Where exists([Order ID]);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[tonytang]

Thanks for your reply. But,  if i dont wanna change the order in which I  load the tables, what should I do? Is there any other ways to solve it without changing the loading order?

Thanks so much!

[rwunderlich · Accepted solution]

If all the tables are already loaded, you can do it with something like this:

ProductMap:
Mapping Load  [Product Sub-Category],  [Product Name] Resident Order;

Inner Join (Order) 
Load [Order ID]
Resident Order
Where ApplyMap('ProductMap', [Product Sub-Category]) <> 'ABC';

Right Keep (Transaction) Load [Order ID] Resident Order;

-Rob

BTW, I'll be teaching an advanced scripting course online on 16 Nov https://masterssummit.com/

[tonytang]

Thanks a lot. It  really works.

Yes, let me see the course detail then decide later. Thanks