3 Replies Latest reply: Dec 15, 2011 8:33 AM by Miguel Angel Baeyens de Arce RSS

    Lookup Values

      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 de Arce

          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

              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 de Arce

                  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