topic Re: Filter data in script in QlikView

Filter data in script

Thu, 13 Feb 2014 17:46:49 GMT

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

Re: Filter data in script

Nicole-Smith — Thu, 13 Feb 2014 17:57:59 GMT

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;

Re: Filter data in script

maxgro — Thu, 13 Feb 2014 18:13:36 GMT

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;

Re: Filter data in script

rbecher — Thu, 13 Feb 2014 19:32:59 GMT

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

Re: Filter data in script

Thu, 13 Feb 2014 22:23:07 GMT

Thanks everyone,

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

Re: Filter data in script

rbecher — Thu, 13 Feb 2014 22:30:13 GMT

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

Re: Filter data in script

haquenasrin08 — Wed, 18 Sep 2019 13:30:01 GMT

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;