topic Re: Multiple columns referring to same table in QlikView

Multiple columns referring to same table

Tue, 11 Nov 2014 09:04:58 GMT

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)

faultId	Description	createdById	modifiedById
001	description001	userId001	userId002
002	description002	userId003	userId003
003	description003	userId004	null

the second contains users and looks like this:

userId	username	emailAddress
userId001	daveP	davep@mail.com
userId002	peterK	peterk@mail.com
userId003	fredC	fredc@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,

Re: Multiple columns referring to same table

alexandros17 — Tue, 11 Nov 2014 09:13:43 GMT

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

Re: Multiple columns referring to same table

jonathandienst — Tue, 11 Nov 2014 09:24:14 GMT

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

Re: Multiple columns referring to same table

ychaitanya — Tue, 11 Nov 2014 09:26:40 GMT

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

Use join

Thanks

Chaitanya

Re: Multiple columns referring to same table

Tue, 11 Nov 2014 09:26:50 GMT

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 (?).

Re: Multiple columns referring to same table

Tue, 11 Nov 2014 10:00:04 GMT

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?

Re: Multiple columns referring to same table

jonathandienst — Wed, 12 Nov 2014 05:25:52 GMT

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