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

          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