3 Replies Latest reply: Feb 19, 2015 8:25 AM by Henric Cronström RSS

    Fieldvalue or Firstsortedvalue instead of LEFT JOIN

      Hi,

       

      I'm trying to optimize my transformation and would appreciate some pointers how to approach this:

       

      What I'm aiming for is to make this quicker in QV and I would like to have all logic in the transformation layer.

       

      SALES_FACT:

      LOAD

           num(SalesFactId) AS %SalesFactId,

          

           /*Dimension ID*/

           num(D_CONTRACT_ID) AS %D_CONTRACT_ID,

           num(DATE#(D_EVENT_DATE_ID,'YYYYMMDD')) AS %D_EVENT_DATE_ID,

           if(EventTypeCd = 'INSURANCEANNULATED', 1,0) AS FlagINSURANCEANNULATED,

           if(EventTypeCd = 'INSURANCECANCELLED', 1,0) AS FlagINSURANCECANCELLED,

           if(EventTypeCd = 'INSURANCEOFFER', 1,0) AS FlagTempINSURANCEOFFER,

           if(EventTypeCd = 'INSURANCESOLD', 1,0) AS FlagTempINSURANCESOLD,

           if(MATCH([CustomerFeedback], 'DECLINED', 'ACCEPTED', 'NEVER'),1,0) AS FlagCustomerFeedbackNoCallingList,

      FROM

      SALES_FACT.qvd (qvd);

       

      LEFT JOIN (SALES_FACT)

       

      LOAD %SalesFactId,

           1 as FlagNoCallingList

      RESIDENT SALES_FACT

      WHERE

      FlagCustomerFeedbackNoCallingList = 1 OR FlagTempINSURANCESOLD = 1 OR FlagINSURANCEANNULATED = 1 OR FlagINSURANCECANCELLED = 1;

       

      LEFT JOIN (SALES_FACT)

       

      LOAD max(%SalesFactId) AS %SalesFactId,

           1 as FlagINSURANCEOFFER

      RESIDENT SALES_FACT

      where FlagTempINSURANCEOFFER = 1

      group by %D_CONTRACT_ID;

       

      The reason for creating FlagNoCallingList is to use one simple set analysis in the dashboard to exclude those contracts that have FlagNoCallingList = 1

       

      LOAD max(%SalesFactId) AS %SalesFactId,

           1 as FlagINSURANCEOFFER

      RESIDENT SALES_FACT

      where FlagTempINSURANCEOFFER = 1

      group by %D_CONTRACT_ID;

       

      is because we have duplicates in the source table (we are working on removing these) and I always want the latest record for FlagTempINSURANCEOFFER for each %D_CONTRACT_ID.

       

      So is it possible to replace these logic with smarter scripting in QV. 

       

      Maybe:

      load

        aggr(max(fieldvalue('%SalesFactId', recno())),%D_CONTRACT_ID) 

      AutoGenerate FieldValueCount('%SalesFactId');

       

      or

       

      FIRSTSORTEDVALUE (??, aggr(max(%SalesFactId),%D_CONTRACT_ID) )

       

      Br Johan

        • Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN
          Henric Cronström

          You should avoid the joins.

           

          The script below does the same - I think.

           

          HIC

           

          // Find highest SalesFactID
          MaxSalesFactId:
          LOAD
          max(SalesFactId) AS MaxSalesFactId
          FROM SALES_FACT.qvd (qvd)
          Where EventTypeCd = 'INSURANCEOFFER';

          Let vMaxSalesFactId = Peek('MaxSalesFactId',-1,'MaxSalesFactId');

          //Load Fact table
          SALES_FACT:
          Load *,
          If(%SalesFactId = $(vMaxSalesFactId),1,0) as FlagINSURANCEOFFER,
          If(FlagCustomerFeedbackNoCallingList = 1 OR FlagTempINSURANCESOLD = 1 OR
          FlagINSURANCEANNULATED = 1 OR FlagINSURANCECANCELLED = 1,
          1, 0)
          as FlagNoCallingList;
          LOAD
          num(SalesFactId) AS %SalesFactId,
          /*Dimension ID*/
          num(D_CONTRACT_ID) AS %D_CONTRACT_ID,
          num(DATE#(D_EVENT_DATE_ID,'YYYYMMDD')) AS %D_EVENT_DATE_ID,
          if(EventTypeCd = 'INSURANCEANNULATED', 1,0) AS FlagINSURANCEANNULATED,
          if(EventTypeCd = 'INSURANCECANCELLED', 1,0) AS FlagINSURANCECANCELLED,
          if(EventTypeCd = 'INSURANCEOFFER', 1,0) AS FlagTempINSURANCEOFFER,
          if(EventTypeCd = 'INSURANCESOLD', 1,0) AS FlagTempINSURANCESOLD,
          if(MATCH([CustomerFeedback], 'DECLINED', 'ACCEPTED', 'NEVER'),1,0) AS FlagCustomerFeedbackNoCallingList,
          FROM SALES_FACT.qvd (qvd);

            • Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN

              Hi Henric, I would like to avoid joins but in this case the MaxSalesFactId will just generate one value for one %D_CONTRACT_ID (Agreement) right? I need the max value for each %D_CONTRACT_ID, so if I do a group by %D_CONTRACT_ID it will be provided. But then I can't use variables right?

               

              Johan

              --------------------------------------------------------------------------------------------

              You should avoid the joins.

               

              The script below does the same - I think.

               

              HIC


              // Find highest SalesFactID
              MaxSalesFactId:
              LOAD
              max(SalesFactId) AS MaxSalesFactId
              FROM SALES_FACT.qvd (qvd)
              Where EventTypeCd = 'INSURANCEOFFER';

              Let vMaxSalesFactId = Peek('MaxSalesFactId',-1,'MaxSalesFactId');

                • Re: Fieldvalue or Firstsortedvalue instead of LEFT JOIN
                  Henric Cronström

                  If you want the max value for each ContractID, then you can use a join, yes. A variable can only have one value. But then you need ContractID as key in your table - which you don't in your example above.

                   

                  LOAD

                       %D_CONTRACT_ID,

                       Max(%SalesFactId) AS %SalesFactId,

                       1 as FlagINSURANCEOFFER

                       RESIDENT SALES_FACT

                       where FlagTempINSURANCEOFFER = 1

                       group by %D_CONTRACT_ID;

                   

                  HIC