Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
copperfinish
New Contributor

Filtering data in both dimension and Fact table

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.

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Filtering data in both dimension and Fact table

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');

View solution in original post

13 Replies
vishsaggi
Esteemed Contributor III

Re: Filtering data in both dimension and Fact table

Can we see your script?

m_perreault
Contributor III

Re: Filtering data in both dimension and Fact table

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...

copperfinish
New Contributor

Re: Filtering data in both dimension and Fact table

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
Esteemed Contributor III

Re: Filtering data in both dimension and Fact table

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');

View solution in original post

copperfinish
New Contributor

Re: Filtering data in both dimension and Fact table

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
Esteemed Contributor III

Re: Filtering data in both dimension and Fact table

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;

copperfinish
New Contributor

Re: Filtering data in both dimension and Fact table

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'

Highlighted
m_perreault
Contributor III

Re: Filtering data in both dimension and Fact table

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;

copperfinish
New Contributor

Re: Filtering data in both dimension and Fact table

This worked. Thank you.