Filtering data in both dimension and Fact table

[Anonymous]

I am a week old to Qlikview and yet to take developer training so my question are very basic and answers must be very obvious (but unfortunately not to me :-(. Right now I'm struggling with my data model. I have two tables in my data model so far - a fact and a dimension. I do not want to bring in records with a certain status type into my data load at all as they will never be used yet they exist in my DB. I have added a WHERE clause to my dimension table load script like so: WHERE Item_status_code <> ('DELETED') and Item_status_code <> ('INACTIVE'). Now what is happening is a new status category is being created with code type as '-' which groups rows records from fact tables that doesn't have matching records in Fact Table. Fact and dimension table have one to one relationship. How do I stop the records being brought in from fact table as well is there are no matching records in DIM table.

[vishsaggi]

Can we see your script?

[m_perreault]

It is difficult to understand without seeing your script however I believe a keep function could be useful to you.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

[Anonymous]

Sure. My scripts are:

Load "Item_ID";

SQL SELECT *

FROM FactTransaction;

Load "Item_ID",

"Item_status_code";

SQL SELECT *

FROM FootLoose.dbo.dimItem

WHERE Item_status_code <> ('DELETED') and Item_status_code <> ('INACTIVE');

[vishsaggi · Accepted solution]

Try this AND will not work here. Use OR

Load "Item_ID";

SQL SELECT *

FROM FactTransaction;

Load "Item_ID",

"Item_status_code";

SQL SELECT *

FROM FootLoose.dbo.dimItem

WHERE Item_status_code <> ('DELETED') OR Item_status_code <> ('INACTIVE');

[Anonymous]

This is not working.

I want the filter to remove rows both from fact table and dimension table. The field I want to use in WHERE clause only appears in dimension table.

[vishsaggi]

Try any one of these below?

//1st Way

Fact:

Load "Item_ID";

SQL SELECT *

FROM FactTransaction;

Right Keep

Dim:

Load "Item_ID",

"Item_status_code";

SQL SELECT *

FROM FootLoose.dbo.dimItem

WHERE Item_status_code <> ('DELETED') OR Item_status_code <> ('INACTIVE');

//2nd way

Dim:

Load "Item_ID",

"Item_status_code";

SQL SELECT *

FROM FootLoose.dbo.dimItem

WHERE Item_status_code <> ('DELETED') OR Item_status_code <> ('INACTIVE');

Fact:

Load "Item_ID"

WHERE Exists("Item_ID");

SQL SELECT *

FROM FactTransaction;

[Anonymous]

Wouldn't "Keep" function force me to combine two tables? I want a separate dim and fact tables but results to match below SQL script. 

Select distinct Item_status_code,

Count(dimItem.Item_ID)

FROM dimItem

join FactTransaction ON dimItem.Item_ID = FactTransaction.Item_ID

WHERE Item_status_code <> '_DELETED_' AND Item_status_code <> '_INACTIVE_'

Group By tkt_custmr_reqst_type_cd

Instead what it is doing right now is ignoring my filter WHERE clause and doing either

1. Brings all results back

2. Creates a separate entity for Item Status '-' that groups together 1821 rows from fact table that actually have Item Status as 'Inactive' or 'Deleted'

[m_perreault]

Keep won't combine two tables.  See below from Qlik's help

The keep prefix is similar to the join prefix. Just as the join prefix, it compares the loaded table with an existing named table or the last previously created data table, but instead of joining the loaded table with an existing table, it has the effect of reducing one or both of the two tables before they are stored in QlikView, based on the intersection of table data. The comparison made is equivalent to a natural join made over all the common fields, i.e. the same way as in a corresponding join. However, the two tables are not joined and will be kept in QlikView as two separately named tables.

Try this

Table1:

Load "Item_ID",

"Item_status_code";

SQL SELECT *

FROM FootLoose.dbo.dimItem

WHERE Item_status_code <> ('DELETED') and Item_status_code <> ('INACTIVE');

Left Keep (Table1)

Table2:

Load "Item_ID";

SQL SELECT *

FROM FactTransaction;

[Anonymous]

This worked. Thank you.