How to exclude value from dimension and fact table

[#1]

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

[Anil_Babu_Samineni]

May be this?

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

[mostwanted123]

Try this,

Load ProductKey, ProductCode

From DimProduct.QVD (qvd)

Where not wildmatch(ProductCode,'C');

Regards,

Pratik

[#4]

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

[dinuwanbr]

Hi minh,

Try this

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

or

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