Exclude SKU's from data load in multiple views, best approach?

[chriscools]

Hello,

i have a question about how to set up my data load.

There are multiple views on the SQL database, one with all the SKU's and then multiple views for transaction types (salesorders, purchaseorders, complaints, stock movements,...) that all are added to the fact table.

We have 12 different sales families in our product range, and 2 of them don't need to be included in the data load.

And the 2 salesfamilies that don't need to be included in the dataload are present in  all the other views.

What would be the best option to exclude the unwanted salesfamilies?

1: exclude them in all the sql views before they go to qlikview?

2: is there an option to exclude these salesfamilies and all related facts after the data load has been completed?

How should i approach this best?

thanx a lot!

Chris

[rubenmarin · Accepted solution]

Hi @chriscools, there are different options, first you need a table with the SKUs you want to load, then I would use "Where Exists(SKUField)" when loading other tables.

Another option can be using (Left/Right) keep with this table to only load SKUs that exists in this table.

And other option can be using section access to restrict access to undesired values.

If the sql views are dedicated to this app, filtering in the views is a viable option. As a QV developer I usually extract all raw data and do the filters myself in the next layer but in some situations can be better to have it filtered on source data.

[chriscools]

Hello Ruben,

Thank you for the different options!

I'll guess i'll try the where exists one, so i can leave my views unaltered and reuse them for other purposes where the unwanted sku's have their use.

This left /right keep option is this comparable with a left/right join? or is that something different?

If these type of filters are used in the Data load in qlikview does this also minimise load for the SQL server?

or does it query all the data and throws the unwanted data away in qlikview ?

grtz,

Chris

[rubenmarin]

Hello, these are QlikView script options, Keep is similar to Join but it maintains two different tables, keeping only the reocords that exists in one or both tables.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

Both of them will query all data, but if have a first layer of qvds with raw data, in a 2nd or 3rd layer of qvds you can do a "LOAD * From [qvd] Where Exists(Field)"; this sentence can do an optmized load of a qvd, wich is the faster way to load data from a qvd.

After loading the reduced data you can work with the loaded table to  do the required trasformations.

If this SKUs will be never used in any QV app and there is a lot of data of this SKUs  probably will be better to have them flagged in sql and avoid to load them on first layer (or in your app).

[chriscools]

Hello,

I'm trying the where exists option, but i get the message that i'm using an incorrect syntax.

Is it possible that this solution doesn't work directly on the sql data and that i need to use it on a qvd file?

grtz,

chris

[rubenmarin]

Hello, Exists() should be in qlik script, that is in the LOAD sentence, not in the SELECT sentence. It's what I tried to say in the 2nd paragraph of my last post.

[chriscools]

Goodmorning,

okay thanx, that works! 

i didn't pay attention to the part that it needed to be included in the load statement and i searched extra information online first, but it didn't catch my eye that it was in the wrong part.

thanx a lot!

grtz,

chris