5 Replies Latest reply: Feb 16, 2014 10:27 AM by Ralf Becher RSS

    Filter data in script

    Arturo Perez

      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

          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
            Massimo Grossi

            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
              Ralf Becher

              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