Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
MVP
MVP

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;

View solution in original post

6 Replies
Highlighted

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;

Highlighted
MVP
MVP

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;

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Highlighted
Not applicable

Re: Filter data in script

Thanks everyone,

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Highlighted
New Contributor

Re: Filter data in script

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;