topic Re: How to exclude value from dimension and fact table in QlikView

How to exclude value from dimension and fact table

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have 2 reports (A, B) using 1 data model with dimension and fact table like:

Dim:

ProductKey ProductCode

1 A

2 B

3 C

Fact:

ProductKey Price

1 9

2 11

3 33

In report A, I want to remove product code value 'C' for both Dim and Fact table then i use:

Load ProductKey, ProductCode

From DimProduct.QVD (qvd)

Where ProductCode <> 'C'

But in chart when i sum(Price) i expected 20 value but it is 53 indeed.

Is any one have idea to exclude 'C' product code in both dim and fact table.

Thanks

Re: How to exclude value from dimension and fact table

Anil_Babu_Samineni — Wed, 20 Sep 2017 15:23:56 GMT

May be this?

Sum({<ProductCode -= {'C'}>} Price)

Re: How to exclude value from dimension and fact table

mostwanted123 — Wed, 20 Sep 2017 16:03:58 GMT

Try this,

Load ProductKey, ProductCode

From DimProduct.QVD (qvd)

Where not wildmatch(ProductCode,'C');

Regards,

Pratik

Re: How to exclude value from dimension and fact table

Thu, 21 Sep 2017 03:07:14 GMT

Thx All,

I finally find the solution: in the Load script for Fact we create a table

Dim_Exclude:

LOAD

ProductKey as ProductKeyExclude,

FROM DimProduct.QVD (qvd)

Where ProductCode = 'RCT';

And in load Fact table we use.

New_Facts:

LOAD

ProductKey,

ProductCode

FROM

Fact.QVD (qvd)

WHERE NOT EXISTS(ProductKeyExclude, ProductKey );

Re: How to exclude value from dimension and fact table

dinuwanbr — Thu, 21 Sep 2017 03:17:15 GMT

Hi minh,

Try this

sum({<ProductCode ={*}-{'C'}>} Price)

sum({<ProductCode -= {'C'}>} Price).

Other wise while loading do this

Dim:

Load * from DimSource where ProductCode <>'C';

inner join

Fact:

Load * from FactSource;

Rgds,

Tharindu