topic Re: Lookup Values in QlikView

Lookup Values

Tue, 13 Dec 2011 21:11:57 GMT

My QV is used to analyze message conversations. I have a column identified as UserID to identify People. I also have columns such as To and From to identify users who have sent the message and users that have received the message. How can I load the tables to use the UserIDs in the To and From columns.

Thanks for your help.

Eric

Re: Lookup Values

Miguel_Angel_Baeyens — Tue, 13 Dec 2011 23:32:47 GMT

Hi Eric,

Is something like this what you mean?

FromMap:
MAPPING LOAD UserID,
     From
FROM FromSource;
MessageData:
LOAD *,
     ApplyMap('FromMap', UserID) AS FromField
FROM MessageSource;

This will create a new field called "FromField", which will get the From corresponding to that UserID. The example is quite simple but you get the idea. Probably using the MessageID woul work better, depending on your data model.

Hope that helps.

Miguel

Lookup Values

Wed, 14 Dec 2011 18:26:21 GMT

This is helpful. However, there are two tables in our situation. One is a Users table which includes the UserID. The other tables is a Messages Table which includes the FromID. The FromID is a UserID, but I cannot get the two IDs to link so that when I click a UserID in the Users table, the Messages table updates with only messages from that user. I would consider making them have the same name, but there are other fields that also use the UserID to refer to another user that is involved in that message.

Re: Lookup Values

Miguel_Angel_Baeyens — Thu, 15 Dec 2011 13:33:25 GMT

Hi,

Based on the example above, you can always "repeat" your fields with different names to avoid joining or concatenation:

MessagesMap:
MAPPING LOAD MessageID,
     UserID
FROM FromSource
WHERE UserType = 'From';

Users:
LOAD UserID,
     ApplyMap('MessagesMap', MessageID) AS FromID
FROM MessageSource;

Hope that helps.

Miguel