4 Replies Latest reply: May 18, 2011 11:04 AM by Andrew Sloan RSS

    Data not matching

      Hi -

       

      I am bringing data together from 2 sources (a dialler and dynamics CRM)

       

      In the dialler a GUID was split into 2 fields as it was too long so I have used

       

       

      CONCAT(`CALL_DATA1`,`CALL_DATA2`) as GUID,

       

       

      to bring them together and use as the matching key in dynamics CRM.

      The problem I have is that some records are matching and some are not - when I look at the non matching in Dynamics the GUID is the same as the concatenated field in Qlikview but the information wont show.

      Is this a conversion issue and if so could anyone help with it?

      Thanks

      Andy

       

       

       

       

       

       

       

       

        • Data not matching
          Paul Nockolds

          Hi Andy

           

          Can you not just do...

           

          Trim(Call_Data1) & Trim(Call_Data2) as GUID

            • Data not matching

              Sorry Paul - it doesnt retieve any data with that, I think its because our dialler is in MySQL and Dynamics SQL!

               

              Any other thoughts?

                • Data not matching
                  Paul Nockolds

                  What does your LOAD script look like?

                    • Data not matching

                      ///$tab Main
                      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;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                      ODBC CONNECT TO rostrvmMIS;
                      SQL SELECT `ACCOUNT_ID`,
                          `ACCOUNT_REF`,
                          `ADDR_1`,
                          `ADDR_2`,
                          `ADDR_3`,
                          `ADDR_4`,
                          `ANS_MACHINE_COUNT`,
                          `BUSY_COUNT`,
                          `CALL_ATTEMPTS`,
                          `CALL_BACK_ACT_CDE`,
                          `CALL_BACK_AGENT`,
                          `CALL_BACK_DATE`,
                          `CALL_BACK_FLAG`,
                          `CALL_BACK_TIME`,
                          `CALL_BACK_TIME_LATEST`,
                          `CALL_BACK_TYPE`,
                           CONCAT(CALL_DATA1,CALL_DATA2) as GUID,
                          `CALL_DATA3`,
                          `CALL_DATA4`,
                          `CAMPAIGN_DESC`,
                          `CAMPAIGN_ID`,
                          `DOWN_LOAD_DATE`,
                          `EXCLUSION_RSN`,
                          `LAST_ACT_CODE`,
                          LIST,
                          NAME,
                          `RING_OUT_COUNT`,
                          `TEL_NUM_1`,
                          `TEL_NUM_2`,
                          `TEL_NUM_3`,
                          `TEL_NUM_4`,
                          `TERM_CODE`,
                          `TERM_DESC`
                      FROM mis.`dlr_accounts_hist`;

                      SQL SELECT `CALL_RESULT`,
                          LIST,
                          `SCHED_CB_TYPE`,
                          STAMPEDDATE
                      FROM mis.`dlr_calls_hist`;

                      SQL SELECT `ACCOUNT_ID`,
                          `CALL_START_DATE`,
                          `CALL_TYPE`,
                          `ROW_INDEX`,
                          `SWITCH_REASON_CODE`
                      FROM mis.`agent_call_hist`;

                      ODBC CONNECT TO crmsvr1;
                      SQL SELECT "Brk_DisplayGUID" as GUID,
                          "Brk_Lead_Downloaded_Guide",
                          "Brk_Lead_Where_Heard",
                          "Brk_lead_WhereHeardDetails",
                          CreatedBy,
                          LeadId,
                          MasterId,
                          MasterLeadIdDsc,
                          OwnerId,
                          StateCode,
                          StatusCode
                      FROM "Brookson_MSCRM".dbo.Lead;

                       

                      Ive kept it simple until I can sort the GUID issue out!