topic Re: Filtering data in both dimension and Fact table in QlikView

Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 17:30:17 GMT

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.

Re: Filtering data in both dimension and Fact table

vishsaggi — Mon, 14 May 2018 17:54:32 GMT

Can we see your script?

Re: Filtering data in both dimension and Fact table

m_perreault — Mon, 14 May 2018 18:00:13 GMT

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/Keep.htm

Re: Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 18:09:15 GMT

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

Re: Filtering data in both dimension and Fact table

vishsaggi — Mon, 14 May 2018 18:17:13 GMT

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

Re: Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 19:27:06 GMT

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.

Re: Filtering data in both dimension and Fact table

vishsaggi — Mon, 14 May 2018 19:57:35 GMT

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;

Re: Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 20:05:01 GMT

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'

Re: Filtering data in both dimension and Fact table

m_perreault — Mon, 14 May 2018 20:10:13 GMT

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;

Re: Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 20:14:51 GMT

This worked. Thank you.

Re: Filtering data in both dimension and Fact table

Anonymous — Mon, 14 May 2018 20:25:41 GMT

This also worked. Must say QlikView community is amazing. I'm so excited to be here!!!

I do have one more question. I am looking at my results in a chart format, the results are correct but the rows fetched when I click on reload are still 1821 more in fact then dimension. Let me know if anyone has thoughts on this. Thank you!

Re: Filtering data in both dimension and Fact table

vishsaggi — Mon, 14 May 2018 20:34:45 GMT

Which script did you use from my thread? Right keep or Exists() ?

Re: Filtering data in both dimension and Fact table

Anonymous — Tue, 15 May 2018 14:21:30 GMT

I tried both and both worked.

Re: Filtering data in both dimension and Fact table

vishsaggi — Tue, 15 May 2018 14:33:00 GMT

Was the issue you were talking about here

"I do have one more question. I am looking at my results in a chart format, the results are correct but the rows fetched when I click on reload are still 1821 more in fact then dimension. Let me know if anyone has thoughts on this. Thank you!"

Is this resolved?