topic Re: several links with the same table in QlikView

several links with the same table

Wed, 31 Jul 2013 09:30:40 GMT

hello,

I've got a simple question about link between two tables.

I am beginner with SQL and I think my question is easy for someone who is not beginner

The first table is like this :

Interactions:

LOAD AccountId ,

Assignee_Support_Initial__c ,

CreatedById ,

LastModifiedById ;

SQL SELECT AccountId,

Assignee_Support_Initial__c,

CreatedById,

LastModifiedById

FROM Case;

Assignee_Support_Initial__c, CreatedById, LastModifiedById are a code where i want to put the name of the user which is on the second table. ( id of the second table is the link with Assignee_Support_Initial__c, CreatedById, LastModifiedById )

operateur:

LOAD Email,

FirstName ,

Id ,

LastName;

SQL SELECT Email,

FirstName,

Id,

LastName

FROM User;

I would like to add the lastname in the interactions table (join left?). how i can do that?

AccountId ,

Assignee_Support_Initial__c ,

Lastname_Assignee_Support_Initial,

CreatedById,

Lastname_Assignee_Support_Initial

LastModifiedById,

Lastname_LastModifiedById

thank you

stephane

Re: several links with the same table

Wed, 31 Jul 2013 10:12:37 GMT

Hi,

Try this.

Interactions:

LOAD AccountId ,

Assignee_Support_Initial__c ,

CreatedById ,

LastModifiedById,

Assignee_Support_Initial__c & CreatedById & LastModifiedById as Id;

;

SQL SELECT AccountId,

Assignee_Support_Initial__c,

CreatedById,

LastModifiedById

FROM Case;

LEFT JOIN

LOAD Email,

FirstName ,

Id ,

LastName;

SQL SELECT Email,

FirstName,

Id,

LastName

FROM User;

Re: several links with the same table

Wed, 31 Jul 2013 11:25:33 GMT

Sorry venugopal4s, but it doesn't work.

The value for theses fields are not the same :

Assignee_Support_Initial__c ,

CreatedById ,

LastModifiedById,

that means differents persons could be the actor of theses fields.

The list of the persons are on the seconf table, but I want only to put the right person name for each field in the first table.

this is the result of your proposal :

Re: several links with the same table

Wed, 31 Jul 2013 11:38:53 GMT

I've found it !

i need to do three left join...

Interactions:

LOAD AccountId ,

Assignee_Support_Initial__c as code_operateur_initial ,

CreatedById as code_operateur_creation,

LastModifiedById as code_operateur_derniere_modification;

SQL SELECT AccountId,

Assignee_Support_Initial__c,

CreatedById,

LastModifiedById

FROM Case;

LEFT JOIN

LOAD

FirstName & ' ' & LastName as Nom_operateur_initial ,

Id as code_operateur_initial

;

SQL SELECT

FirstName,

Id,

LastName

FROM User;

LEFT JOIN

LOAD

FirstName & ' ' & LastName as Nom_operateur_creation ,

Id as code_operateur_creation

;

SQL SELECT

FirstName,

Id,

LastName

FROM User;

LEFT JOIN

LOAD

FirstName & ' ' & LastName as Nom_operateur_derniere_modification ,

Id as code_operateur_derniere_modification

;

SQL SELECT

FirstName,

Id,

LastName

FROM User;

and the result :