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

Multiple columns referring to same table

Hey all,

I'm having the following situation:

I have 2 tables in SQL, the first table contains faults and looks like this: (only shown related columns)

faultIdDescriptioncreatedByIdmodifiedById
001description001userId001userId002
002description002userId003userId003
003description003userId004null

the second contains users and looks like this:

userIdusernameemailAddress
userId001davePdavep@mail.com
userId002peterKpeterk@mail.com
userId003fredCfredc@mail.com

Now I can't name the columns in such a way that it would get connected automatically, as that would require me to name both createdById and modifiedById to 'userId' (right?).

How can I connect these tables?

Regards,

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

load twice the second table:

load userid as createdbyid, username as usernamecreate, emailaddress as emailcreate resident ...

load userid as modifiedbyid, username as usernamemodif, emailaddress as emailmodif resident ...

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

If the userid table contains only a few fields (eg id, username and email address as per your post), then you can map these into the main table:

mapUserIdName:

Mapping Load userId, username

From Users;

mapUserIdEmail:

Mapping Load userId, emailAddress

From Users;

Faults:

LOAD faultId,

     ...

     createdById,

     Applymap('mapUserIdName', createdById) As createdByUsername,

     Applymap('mapUserIdEmail', createdById) As createdByEmail,

     modifiedById,

     Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

     Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail,

     ....

From Faults;

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ychaitanya
Creator III
Creator III

use lookup function in the first table..

load second table first

then while loading the first table ... use look up function on the previous loaded table..

OR

Use join

Thanks

Chaitanya

Not applicable
Author

Thanks for your answer!

I suppose that would do. However this creates duplicate data. The user table is not that big so it won't really decrease performance that much. Still I'd think there would be a more elegant solution (?).

Not applicable
Author

This seems the best solution, however I can not get the syntax right. I am using a SQL database, and have incorparated your part of the code in the following way:

mapUserIdName:

Mapping SQL SELECT Id, Username

From "myDataBase".dbo.Users;

mapUserIdEmail:

Mapping SQL SELECT Id, EmailAddress

From "myDataBase".dbo.Users;

Faults:

SQL SELECT ContractorId,

  CreatedAt,

  CreatedById,

  Applymap('mapUserIdName', createdById) As createdByUsername,

  Applymap('mapUserIdEmail', createdById) As createdByEmail,

  ModifiedAt,

  ModifiedById,

  Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

  Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail,

    Description as FaultDescription,

    Id as FaultId

FROM "myDataBase".dbo.Faults;

I recieve the following error:

ErrorMsg: 'Applymap' is not a recognized built-in function name.

How should I apply the mapping instead?

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Everything from the SQL command to the closing ; is sent to the DBMS. Applymap is a QV function, not a SQL function, so you need something like this:

Faults:

LOAD *,

  Applymap('mapUserIdName', createdById) As createdByUsername,

  Applymap('mapUserIdEmail', createdById) As createdByEmail,

  Applymap('mapUserIdName', modifiedById) As modifiedByUsername,

  Applymap('mapUserIdEmail', modifiedById) As modifiedByEmail;

SQL SELECT ContractorId,

  CreatedAt,

  CreatedById,

  ModifiedAt,

  ModifiedById,

  Description as FaultDescription,

  Id as FaultId

FROM "myDataBase".dbo.Faults;

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein