topic Data not matching in QlikView

Data not matching

Wed, 18 May 2011 12:29:33 GMT

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

haymarketpaul — Wed, 18 May 2011 14:51:26 GMT

Hi Andy

Can you not just do...

Trim(Call_Data1) & Trim(Call_Data2) as GUID

Data not matching

Wed, 18 May 2011 14:57:57 GMT

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

haymarketpaul — Wed, 18 May 2011 15:01:46 GMT

What does your LOAD script look like?

Data not matching

Wed, 18 May 2011 15:04:55 GMT

///$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!