topic Re: Qlikview how to delete everything in one table when certain values exist in other tables in QlikView

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

tonytang — Sun, 24 Oct 2021 16:24:18 GMT

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.

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

rwunderlich — Sun, 24 Oct 2021 18:49:40 GMT

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

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

tonytang — Sun, 24 Oct 2021 23:54:53 GMT

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!

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

rwunderlich — Mon, 25 Oct 2021 15:36:23 GMT

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/

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

tonytang — Tue, 26 Oct 2021 10:50:32 GMT

Thanks a lot. It really works.

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