Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi,
Are Id in User's table and OwnerId in Contact's table the same?
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
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.
yes
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
I thought that also, I would assume just a simplified example of code
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
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
Does Salesforce allow you to specify the LEFT JOIN in the SQL script. That would be the easiest option.