Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Filter data in script

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

1 Solution

Accepted Solutions
maxgro
Not applicable

Re: Filter data in script

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;

5 Replies
Nicole-Smith
Not applicable

Re: Filter data in script

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
Not applicable

Re: Filter data in script

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
Not applicable

Re: Filter data in script

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

Not applicable

Re: Filter data in script

Thanks everyone,

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

rbecher
Not applicable

Re: Filter data in script

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