Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (1)
1 Solution

Accepted Solutions
consenit
Contributor II

Re: One field preventing concatenation of two tables

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.

12 Replies
neetha_p
Honored Contributor

Re: One field preventing concatenation of two tables

Hi,

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

Not applicable

Re: One field preventing concatenation of two tables

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
Contributor II

Re: One field preventing concatenation of two tables

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

Re: One field preventing concatenation of two tables

yes

flipside
Valued Contributor II

Re: One field preventing concatenation of two tables

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

Re: One field preventing concatenation of two tables

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

Not applicable

Re: One field preventing concatenation of two tables

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

Re: One field preventing concatenation of two tables

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
Valued Contributor II

Re: One field preventing concatenation of two tables

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