8 Replies Latest reply: Sep 12, 2013 3:03 AM by Tresesco B RSS

    comparing resident tables

      Hi,

       

      I have two resident tables. I want to fetch records from first table(with field sdp_msisdn) which does not contain msisdns in 2nd table(msisdn).How can I achieve this and display the records in a bar chart?

       

      mea_SDP_exists_RPX_NotExists_10_9:

       

       

      LOAD MER_ID as SDP_MER_ID_10_9,

           MER_FROM_DTTM as SDP_MER_FROM_DTTM_10_9,

           MER_TO_DTTM as MER_TO_DTTM_10_9,

          SDP_MSISDN,

           SDP_MSISDN_10_9,

           SDP_ACCOUNT_ID as SDP_ACCOUNT_ID_10_9,

           SDP_MIN_SERVICE_CLASS as SDP_MIN_SERVICE_CLASS_10_9,

           SDP_MIN_NAME as SDP_MIN_NAME_10_9,

           SDP_MIN_BALANCE as SDP_MIN_BALANCE_10_9,

           PSP_STATUS as PSP_STATUS_10_9,

           PSP_INIT_ACT_DT as PSP_INIT_ACT_DT_10_9,

          // PPD_STATUS as PPD_STATUS_10_9,

          if(PPD_STATUS='','D',if(PPD_STATUS='-','D',PPD_STATUS)) as PPD_STATUS_10_9,

           PPD_STS_CHANGED_TIME as PPD_STS_CHANGED_TIME_10_9

      FROM

      [\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\331662_10-9.txt]

      (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

       

       

      In_RPX_Next_Day:

       

       

      LOAD PAS_ID as PAS_ID_TG,

           STS_ID,

           SFL_ID,

           RUN_ID,

           SUB_ID,

           ACTIVATION_DATE,

           IMEI,

           IMSI,

           IN_ID,

           STATUS,

           STATUS_CHANGED_TIME,

           MSISDN,

           RATE_PLAN_ID,

           PROMO_CODE,

           DEC_RATE,

           BAL_EXPIRY_DATE,

           TEST_NUM,

           LAST_UPDATE,

           SARAS_UPDATE_TIME,

           UPDATE_FILE_NAME,

           LAST_REFILL,

           FOCAL_DATE,

           DEACT_ID,

           EXP_FLAG,

           ZERO_BAL_FLAG,

           SIM,

           TOTAL_REFILL,

           PORT_TYPE,

           CDR_LAST_UPDATE,

           REG_STATUS,

           LAST_REG_STATUS_UPDATE,

           TOTAL_CALL_MIN,

           TOTAL_SMS,

           TOTAL_CHARGE,

           BAL_AFTER,

           FIRST_CALL,

           COUNT_CALLS,

           REACT_ID,

           ESCHEATED_COUPON,

           REACT_DAYS_ID,

           BIT_REACT_REFILL,

           BIT_ADDS,

           BIT_DAILY_ADD,

           BIT_DISC,

           BIT_ADJ,

           BIT_DEACT,

           BIT_REACT,

           BIT_ENDSUB,

           MSISDN_1

      FROM

      [\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\Timing GAp_Next Day in RPX.txt]

      (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

        • Re: comparing resident tables
          Jonathan Dienst

          Hi

           

          If you can change the load order, first load the second table, and then load the first table with this where clause:

           

               Where Not(Exists(MSISDN, SDP_MSISDN))

           

          Regards

          Jonathan

          • Re: comparing resident tables
            Tresesco B

            Try this:

            In_RPX_Next_Day:

             

             

            LOAD PAS_ID as PAS_ID_TG,

                 STS_ID,

                 SFL_ID,

                 RUN_ID,

                 SUB_ID,

                 ACTIVATION_DATE,

                 IMEI,

                 IMSI,

                 IN_ID,

                 STATUS,

                 STATUS_CHANGED_TIME,

                 MSISDN,

                 RATE_PLAN_ID,

                 PROMO_CODE,

                 DEC_RATE,

                 BAL_EXPIRY_DATE,

                 TEST_NUM,

                 LAST_UPDATE,

                 SARAS_UPDATE_TIME,

                 UPDATE_FILE_NAME,

                 LAST_REFILL,

                 FOCAL_DATE,

                 DEACT_ID,

                 EXP_FLAG,

                 ZERO_BAL_FLAG,

                 SIM,

                 TOTAL_REFILL,

                 PORT_TYPE,

                 CDR_LAST_UPDATE,

                 REG_STATUS,

                 LAST_REG_STATUS_UPDATE,

                 TOTAL_CALL_MIN,

                 TOTAL_SMS,

                 TOTAL_CHARGE,

                 BAL_AFTER,

                 FIRST_CALL,

                 COUNT_CALLS,

                 REACT_ID,

                 ESCHEATED_COUPON,

                 REACT_DAYS_ID,

                 BIT_REACT_REFILL,

                 BIT_ADDS,

                 BIT_DAILY_ADD,

                 BIT_DISC,

                 BIT_ADJ,

                 BIT_DEACT,

                 BIT_REACT,

                 BIT_ENDSUB,

                 MSISDN_1

            FROM

            [\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\Timing GAp_Next Day in RPX.txt]

            (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

             

            mea_SDP_exists_RPX_NotExists_10_9:

             

             

            LOAD MER_ID as SDP_MER_ID_10_9,

                 MER_FROM_DTTM as SDP_MER_FROM_DTTM_10_9,

                 MER_TO_DTTM as MER_TO_DTTM_10_9,

                SDP_MSISDN,

                 SDP_MSISDN_10_9,

                 SDP_ACCOUNT_ID as SDP_ACCOUNT_ID_10_9,

                 SDP_MIN_SERVICE_CLASS as SDP_MIN_SERVICE_CLASS_10_9,

                 SDP_MIN_NAME as SDP_MIN_NAME_10_9,

                 SDP_MIN_BALANCE as SDP_MIN_BALANCE_10_9,

                 PSP_STATUS as PSP_STATUS_10_9,

                 PSP_INIT_ACT_DT as PSP_INIT_ACT_DT_10_9,

                // PPD_STATUS as PPD_STATUS_10_9,

                if(PPD_STATUS='','D',if(PPD_STATUS='-','D',PPD_STATUS)) as PPD_STATUS_10_9,

                 PPD_STS_CHANGED_TIME as PPD_STS_CHANGED_TIME_10_9

            FROM

            [\\prdfsdpo02\vdifolders$\SMujumd3\Desktop\331662_10-9.txt]

            (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) Where Not Exists( SDP_MSISDN,MSISDN);