Filter data in script

[#1]

Hi everyone,

I'm trying to filter down a binary load and am stuck.  I want to pull in a binary load from a QVW that contains this data model and then filter it so that I only keep data from factWebActivity where dimWebProperty.WebProperty = 'ETUS' OR dimSalesOp.SalesOp_ProductMarketCode = 'ETUS'.  You should know this a small part of a larger data model that expands from this fact table.

Here is a simple version of the data model I binary load into the QVW.

factWebActivity:

LOAD * INLINE [

    AssociatedSalesOp_Key, WebProperty_Key, ViewCount

    1, 1, 100

    0, 1, 200

    2, 0, 600

];

dimWebProperty:

LOAD * INLINE [

    WebProperty_Key, WebProperty

  0, NULL

  1, ETUS

];

dimSalesOp:

LOAD * INLINE [

    AssociatedSalesOp_Key, SalesOp_ProductMarketCode

  0, NULL

  1, ETUS

  2, EAUS

  3, ETCA

];

I tried creating a temp table from the resident fact table and then joining in the columns I want from the resident dim tables.  My intention was to have all the columns in the same table and then filter on them but it appears that trying this will multiply out the records of the new table through a cartesian join.  It was unexpected behavior but I assume that it considers the join as a third instance of the joining key and creates a circular reference.

My real question is how do I filter down this fact table as defined up top.

Thanks in advance,

Arturo

[Nicole-Smith]

After your binary load:

factWebActivityFinal:

NOCONCATENATE LOAD *

RESIDENT factWebActivity

WHERE lookup('WebProperty', 'WebProperty_Key', WebProperty_Key, 'dimWebProperty') = 'ETUS' or

      lookup('SalesOp_ProductMarketCode', 'AssociatedSalesOp_Key', AssociatedSalesOp_Key, 'dimSalesOp') = 'ETUS';

DROP TABLE factWebActivity;

[maxgro · Accepted solution]

Map1: Mapping LOAD WebProperty_Key, WebProperty resident dimWebProperty;

Map2: Mapping LOAD AssociatedSalesOp_Key, SalesOp_ProductMarketCode Resident dimSalesOp;

Table:

NoConcatenate

load *

Resident factWebActivity

where

  ApplyMap('Map1', WebProperty_Key, 'N/A') = 'ETUS'

  or ApplyMap('Map2', AssociatedSalesOp_Key, 'N/A') = 'ETUS'  ;

DROP Table factWebActivity;

[rbecher]

Hi Arturo,

this works with KEEP:

Binary Base.qvw;

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

Temp1:
Inner Keep (dimWebProperty) LOAD 'ETUS' as WebProperty AutoGenerate(1);

Temp2:
Inner Keep (dimSalesOp) LOAD 'ETUS' as SalesOp_ProductMarketCode AutoGenerate(1);

Let vWebProperty_key = peek('WebProperty_Key', 0, 'dimWebProperty');
Let vSalesOp_key = peek('AssociatedSalesOp_Key', 0, 'dimSalesOp');

Temp3:
NoConcatenate LOAD
    WebProperty_Key,
    AssociatedSalesOp_Key
    Resident factWebActivity
    Where WebProperty_Key = $(vWebProperty_key) OR AssociatedSalesOp_Key = $(vSalesOp_key);

Temp4:
Inner Keep (factWebActivity) LOAD * Resident Temp3;

Drop Tables Temp1, Temp2, Temp3, Temp4;

But to reduce the facts could also mean to adjust the Where-Clause of the Temp3 LOAD to hide all other dim values than NULL and ETUS:

	Where (WebProperty_Key = 0 OR WebProperty_Key = $(vWebProperty_key))
	AND (AssociatedSalesOp_Key = 0 OR AssociatedSalesOp_Key = $(vSalesOp_key));

- Ralf

[#5]

Thanks everyone,

I was able to get Nicole and Massimo's solution to work for me.

[rbecher]

I think my solution with KEEP should be faster with real amount of data. I also wonder what the user says if no facts are shown for the still existing dimenion entries (if not reduced)..

[haquenasrin08]

i need help to understand this Script.

LET LastUpdatedDate=peek('MaxDatefilter',0,'LastUpdatedDate');

if trim(Date(today(),'DD'))=-1 

AND 

if trim(Date(today(),'DD'))<> 32 

AND 

Where DATEFILTER <= $(LastUpdatedDate)-30;