Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

One field preventing concatenation of two tables

I am trying to concatenate two tables, which I have been able to successfully do up to a point. Salesforce is the source of the information.

The problem arises when I try and do the same LEFT JOIN on those individual tables. It is at this point that the [Record Owner] field which I am trying to add is NOT recognised as an identical field, and therefore the tables are not concatenated.

Below is a shortened version of the script


1st TABLE

Contacts:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;



RecordOwners:

LEFT JOIN (Contacts)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;




2nd TABLE

Contacts:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

RecordOwners:

LEFT JOIN (Contacts)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

There are other tables in my script, but this is how the two offending tables end up

export.png

1 Solution

Accepted Solutions
consenit
Partner - Creator II
Partner - Creator II

Hi There.

Try this code:

Contacts1:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

RecordOwners:

LEFT JOIN (Contacts1)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

Contacts2:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

RecordOwners:

LEFT JOIN (Contacts2)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

Contacts:

LOAD * RESIDENT Contacts1;

CONCATENATE LOAD * RESIDENT Contacts2;

DROP TABLE Contacts1;

DROP TABLE Contacts2;

Kind regards,

Ernesto.

View solution in original post

12 Replies
Anonymous
Not applicable
Author

Hi,

Are Id in User's table and OwnerId in Contact's table the same?

Not applicable
Author

Hi Alex,

you need to do your left join after loading both the tables, rather than twice like that. Try the below

1st TABLE

Contacts:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;


LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

LEFT JOIN (Contacts)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

hope that helps

Joe

consenit
Partner - Creator II
Partner - Creator II

Hi There.

Try this code:

Contacts1:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

RecordOwners:

LEFT JOIN (Contacts1)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

Contacts2:

LOAD Id as ContactId,

    AccountId,

    Email,

    Phone,

    Title,

    Inactive_Contact__c,

    OwnerId AS ContactOwnerID,

    Name AS ContactName;

SQL SELECT *

FROM Contact

WHERE AccountId != NULL;

RecordOwners:

LEFT JOIN (Contacts2)

LOAD Id AS ContactOwnerID,

    Name AS [Record Owner];

SQL SELECT *

FROM User;

Contacts:

LOAD * RESIDENT Contacts1;

CONCATENATE LOAD * RESIDENT Contacts2;

DROP TABLE Contacts1;

DROP TABLE Contacts2;

Kind regards,

Ernesto.

Not applicable
Author

yes

flipside
Partner - Specialist II
Partner - Specialist II

Hi Alex,

After the first LEFT JOIN, the table Contacts includes the joined field [Record Owner] and so when you try loading the second table, which you are doing without [Record Owner], it will create it as a new table. I'm not exactly sure what you are trying to do because the two parts of the script look to be doing the same thing.

flipside

Not applicable
Author

I thought that also, I would assume just a simplified example of code

Not applicable
Author

Hi Joe,

the problem with that, is that there are two User tables. Each from a different Salesforce source.

There is a CUSTOM CONNECT command before each table load as you can only connect to one source at a time.

Maybe the solution is to concatenate the User tables and then LEFT JOIN

Not applicable
Author

Hi Alex,

if that is the case then yes you could combine the two before doing your join.

Is this a one to one join on the ID? If that's the case it would be easier to load the user tables as mapping tables and just applymap as you load in the contacts, as you are only loading a single field.

Joe

flipside
Partner - Specialist II
Partner - Specialist II

Does Salesforce allow you to specify the LEFT JOIN in the SQL script. That would be the easiest option.