11 Replies Latest reply: Jun 10, 2016 12:44 PM by Richard Cioci RSS

    Keep Left and Where Exists not eliminating rows in table ??

    Richard Cioci

      Hi Everyone,

       

      I am pulling in some tables via SQL into Qlik from a CRM database. Our subset of customers is much smaller than the entire list of customers available in the CRM, so I have been using the main table of events to eliminate the customers we haven't called on from our customer table.

      I had been previously loading the events first and then loading the customer list with a where exists(customer_id) clause which seemed o be working great. In my table viewer I could see that this dropped the customer list from ~40,000 to ~4,000.

       

      Now for a separate reason, I need to separate this table from the main events table but I still need to keep it reduced. I initially just changed the naming of the common field/key between the two tables, but when I did this the number of customers shot back up to 40,000. I even tried leaving in the original common field and using the where clause and then loading another table using the customer table as resident and then dropping the initial customer table, but it doesn't give me a reduced number of customers.

       

      I have also tried left (keep) to the original event table but it is not reducing the number of customer_id's, and I don't see what I'm doing wrong.

       

      I've included part of my code below:

      Combined is my master event table which I want to use to eliminate unneeded customer_id's from the customer table:

       

      Combined:

      LOAD *,

      date([Call Date] , 'DD-MM-YYYY') as [Call Date Short] //abbreviate out the time leaving the date

      ;

       

      LOAD

      AFFILIATION_ID,

      ALIGNMENT_ID,

      CUSTOMER_ID,

      CUSTOMER_ID as customer_rating_id,

      EMPLOYEE_ID,

      ENTRY_DATE as [Call Entry Date],

      EVENT_ID,

      EVENT_SUB_TYPE,

      EVENT_TYPE,

      INTERACTION_CHANNEL,

      START_DATE_TIME as [Call Date],

      DayStart(START_DATE_TIME) as CalCallDate,

      "ADDRESS_ID",

      "PRODUCT_ID",

      "PRODUCT_ID" as ProdIDDBS,

      "REACTION_TYPE",

      "SEQUENCE",

      CITY as City,

      "LINE_1_ADDRESS" as [Street Address],

      OFF_SUBDIVISION_COD_3 as Province,

      "POSTAL_AREA" as FSA,

      "ALIGNMENT_NAME",

      "TEAM_ID",

      EXTERNAL_ID_1 as AlignID,

      TEAMNAME as Team,

      STATUS_CHANGE_DATE,

      PRODUCT_ID&CUSTOMER_ID&EMPLOYEE_ID as CEP_ID

      ;

      SELECT

        t0.AFFILIATION_ID,

          t0.ALIGNMENT_ID,

          t0.CUSTOMER_ID,

          t0.EMPLOYEE_ID,

          t0.ENTRY_DATE,

          t0.EVENT_ID,

          t0.EVENT_SUB_TYPE,

          t0.EVENT_TYPE,

          t0.INTERACTION_CHANNEL,

          t0.START_DATE_TIME,

          t1."ADDRESS_ID",

          t2."PRODUCT_ID",

          t2."REACTION_TYPE",

          t2."SEQUENCE",

          t4.CITY,

          t4."LINE_1_ADDRESS",

          t4."POSTAL_AREA",

          t4."OFF_SUBDIVISION_COD_3",

          t6."ALIGNMENT_NAME",

          t6."TEAM_ID",

          t6."EXTERNAL_ID_1",

          t7.NAME as TEAMNAME,

          t0.STATUS_CHANGE_DATE

       

       

      FROM

       

       

      WFPROD.EVENT t0,

      WFPROD.AFFILIATION t1,

      WFPROD."EVENT_DETAIL" t2,

      WFPROD.ADDRESS t4,

      WFPROD.ALIGNMENT t6,

      WFPROD.TEAM t7

       

       

      where t0.AFFILIATION_ID = t1.AFFILIATION_ID

      and t0.EVENT_ID = t2."EVENT_ID"

      and t1."ADDRESS_ID" = t4."ADDRESS_ID"

      and t0.START_DATE_TIME >= '$(vPULLDATE)' //YYYY-MM-DD

      and t0.ALIGNMENT_ID = t6.ALIGNMENT_ID

      and t6."TEAM_ID" = t7."TEAM_ID"

      ;

       

       

      // ********** RATING TABLE ********** //

      Rating:

      load

      "CLIENT_ALIGNMENT_ID",

      "SCRAP",

      "PRODID" as ProdID,

      "RATING_VALUE";

      select A.external_id_1 as client_alignment_id,

              C.onekey_id as SCRAP,

              PR.external_id_1 as ProdID,

              R.value_min as rating_value

              //C.customer_id as customer_rating_id

      from WFPROD.rating R

      join WFPROD.dn_rating_attribute D

        on R.dn_rating_attribute_id = d.dn_rating_attribute_id

      join  WFPROD.customer C

        on R.customer_id = C.customer_id

      join WFPROD.alignment A

        on R.alignment_id = A.alignment_id

      left outer join WFPROD.product PR

        on R.product_id = PR.product_id

      left outer join WFPROD.period P

        on R.period_id = P.period_id

      where D.external_id_1 = 'ENGAG'

      and value_min is not null and onekey_id is not null

      ;

       

      Left join (Rating)

      LOAD "ALIGNMENT_ID" as CLIENT_ALIGNMENT_ID,

          "EMPLOYEE_ID" ;

      SQL SELECT "ALIGNMENT_ID",

          "EMPLOYEE_ID"

      FROM WFPROD.ALIGNMENT;

       

       

      // ********** CUSTOMER TABLE ********** //

      Left Keep(Combined)

      Customertmp:

      LOAD "CUSTOMER_ID" as CUSTOMER_ID_CEP,

        "CUSTOMER_ID",

          "CUSTOMER_SUB_TYPE",

          "CUSTOMER_TYPE",

          "FIRST_NAME",

          NAME,

          "ONEKEY_ID",

      FIRST_NAME & ' ' & NAME as [Full Name] //combine names for easier selection

      where exists (CUSTOMER_ID);

      SELECT

          "CUSTOMER_ID",

          "CUSTOMER_SUB_TYPE",

          "CUSTOMER_TYPE",

          "FIRST_NAME",

          NAME,

          "ONEKEY_ID"

        

         From

         WFPROD.CUSTOMER;

       

       

       

      NoConcatenate

      Customer:

      Load CUSTOMER_ID_CEP,

          "CUSTOMER_SUB_TYPE",

          "CUSTOMER_TYPE",

           "ONEKEY_ID",

      [Full Name]

      Resident Customertmp;

      Drop table Customertmp;