3 Replies Latest reply: Feb 6, 2013 10:41 AM by Jonathan Brough RSS

    Derving a field value by lookup to another table

      Morning,

       

      As I'm new to Qlikview I'd appreciate any help with the following:

       

      My script

       

      Qualify *;

      UNQUALIFY 'ID';

       

      Directory;

      Table1:

       

      LOAD [Received],

                [Subject],

                [From],

                Left([Subject],8) as 'ID'

       

      From

      [F:\Terms.xlsx];

      UNQUALIFY *;

       

       

      Qualify *;

      UNQUALIFY 'ID';

       

      Directory;

      Table2:

       

      LOAD [CompanyName] as 'Company',

                [Company Type] as 'Type',

                [Direction'          as 'CompanyDirection',

                [ID]

       

      From

      [F:\Cust.xlsx];

      UNQUALIFY *;

       

       

      Question:

      How can I add a further field to Table2, based upon a lookup to Table1, along the lines of:

       

      If CompanyDirection = Sent, then show the contents of [Received] from Table1? i have tried Lookup and apply map, but didn't get far with either.

       

      Thanks

        • Re: Derving a field value by lookup to another table
          Jonathan Brough

          There are two main ways, mapping tables and joins. Joins have the potential to expand your data (due to cartesian products) so I tend to use mapping tables.

          From your example:

          1. Add the following before Table2 (note, the order of the ID and Received is important):

           

          ReceivedMap:

          MAPPING LOAD ID, Received FROM Table1;

           

          2. Add the following line into your Table 2:

           

               if(Direction='Sent',applymap('ReceivedMap',ID)) AS Table2_Received

           

          Hope that helps and is clear enough. I didn't want to rewrite all your code.

          Jonathan