Concatenating 2 tables into 1

[FrankC]

I am trying to create a MDM solution.  My first table contains the existing records and the second table can contain existing and new records.  I want 1 table that has the existing records plus and new records.  

Table 1 has 36 records and Table 2 has 37. I think my logic is working, but on 1 field (Agent ID) field is displaying a ? for some records even though the data exists.

SalesCenterMDM:

LOAD 

ADPID AS ADPIDTest, 

    ADPID,

Email,

    Name,

    DisplayName, 

Num(AgentID) as AgentID, 

AgentName, 

HiveID, 

ManagerID, 

Supervisor, 

SupervisorOverride,

    Active,

    CreatedAt,

    CreatedBy;

SELECT *

FROM "QLIK_DEV".dbo.SalesCenterMDM;

Concatenate (SalesCenterMDM)

Load Distinct

ADPID

    ,Email

  ,Name

    ,DisplayName

    ,DateStart

    ,Num(AgentID) as AgentID

,AgentName

,HiveID

,ManagerID 

,Supervisor

    ,'' as SupervisorOverride

    ,'' as Active

    ,'' as CreatedAt

    ,'' as CreatedBy

Resident MDM

Where Not Exists(ADPIDTest,ADPID)

[Chanty4u · Accepted solution]

Try this 

Text(Trim(AgentID)) as AgentID

On both loads 

Make sure AgentID exists in both tables and is spelled the same (case-sensitive). Also, scan for nulls or spaces in the AgentID.

Table:

LOAD *,

     Len(AgentID) as AgentID_Len,

     IsNull(AgentID) as IsAgentIDNull

RESIDENT YourTable;