Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

Re: Multiple columns referring to same table

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 ...

MVP
MVP

Re: Multiple columns referring to same table

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

Re: Multiple columns referring to same table

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

Re: Multiple columns referring to same table

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

Re: Multiple columns referring to same table

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?

MVP
MVP

Re: Multiple columns referring to same table

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