topic Re: How do I exclude/delete records in a fact table based on a value in another table? in QlikView

How do I exclude/delete records in a fact table based on a value in another table?

vamshi87 — Fri, 09 Feb 2018 20:38:27 GMT

Fact Table:

Order#	Amount	OrderStatus
1	100	FIRM
2	200	CANCELLED
3	300	DELETED

DeleteOrderStatus:

OrderStatus
CANCELLED
DELETED

Desired Output in Fact Table:

Order#	Amount	OrderStatus
1	100	FIRM

After loading the Fact table, I would like to delete records that have certain OrderStatus that are in 'DeleteOrderStatus' table. Using KEEP/INNER JOIN, I was able to include certain records. Is there a way I can exclude certain records based on another table?

Thanks in advance.

Re: How do I exclude/delete records in a fact table based on a value in another table?

swuehl — Fri, 09 Feb 2018 21:08:01 GMT

You can use the Exists() function in a WHERE clause:

Filter:

LOAD OrderStatus as Status2Exclude

FROM DeleteOrderStatus;

Facts:

LOAD * FROM FactTable

WHERE NOT EXISTS(Status2Exclude, OrderStatus);

Re: How do I exclude/delete records in a fact table based on a value in another table?

michal__ — Sun, 11 Feb 2018 19:06:32 GMT

Exists() is the way to go!

Additionally, if you want to keep your load optimized (if qvd is your source), dont't rename OrderStatus field and use:

where not Exists(OrderStatus). It is faster.

Re: How do I exclude/delete records in a fact table based on a value in another table?

ychaitanya — Mon, 12 Feb 2018 14:29:04 GMT

As Stefan mentioned where not exists() would be the ideal one to handle this . Please let us know if that didn't work

Re: How do I exclude/delete records in a fact table based on a value in another table?

vamshi87 — Mon, 12 Feb 2018 16:07:16 GMT

It worked. Thank you Stefan and Raviteja.

Is there any reason why the field name in the filter was changed? The reason I am asking is when I tried to use the same field name twice as below, the filter did not work properly. Could you please explain why the below ocde does not work?

Filter:

LOAD OrderStatus

FROM DeleteOrderStatus;

Facts:

LOAD * FROM FactTable

WHERE NOT EXISTS(OrderStatus, OrderStatus);

Re: How do I exclude/delete records in a fact table based on a value in another table?

ychaitanya — Mon, 12 Feb 2018 18:08:46 GMT

Hi Vamshi , NOT EXISTS(OrderStatus, OrderStatus); Compares values of Field(1st in arguments) from Current table from the already available field (2nd in the arguments)in the memory..

you could try just where not exists(OrderStatus) if incase you want to use only same field.

Please mark Helpful or Answered so that it helps for the community.

Thanks

Re: How do I exclude/delete records in a fact table based on a value in another table?

vamshi87 — Mon, 12 Feb 2018 18:16:56 GMT

Thank you Raviteja for the clarification.

Re: How do I exclude/delete records in a fact table based on a value in another table?

ychaitanya — Mon, 12 Feb 2018 18:36:10 GMT

Please mark as Answered to close the thread.. Have a great day.

Thanks

Re: How do I exclude/delete records in a fact table based on a value in another table?

swuehl — Mon, 12 Feb 2018 19:53:49 GMT

When using

...

WHERE NOT EXISTS(OrderStatus);

instead of the two argument version, you won't get all requested records if OrderStatus is not a primary key (which I assume it is not).

In other words, as soon as your LOAD encountered the first record with OrderStatus FIRM, FIRM is part of the symbols that the exists() function checks the upcoming record values against. Hence, no other records with OrderStatus FIRM will pass the WHERE clause and will be loaded to the resident output table.

I guess that's what you see when you are mentioning that this approach 'will not work properly', right?

Re: How do I exclude/delete records in a fact table based on a value in another table?

vamshi87 — Wed, 14 Feb 2018 15:48:31 GMT

Thank you Stefan and Chaitanya!