Derving a field value by lookup to another table

[#1]

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

;

UNQUALIFY *;

Qualify *;

UNQUALIFY 'ID';

Directory;

Table2:

LOAD [CompanyName] as 'Company',

          [Company Type] as 'Type',

          [Direction'          as 'CompanyDirection',

          [ID]

From

;

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

[Anonymous]

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

[#3]

I'm afraid it didn't work.

I've attached the script without the Mapping.

Effectively (from the file), I'm trying to add a mapping to GAOS_Extract1 (GAOS_Extract Tab...Row 1), to pick up where there is a corresponding entry (using CPartySDSID as the primary key) in table Outlook_Mailbox (found in Main Tab...row 16).

[Anonymous]

Does this help ? See attached, lines 44-48 and 670. Should have been RESIDENT, not FROM.

You'll need to make sure the Outlook_Mailbox combinations of CPartySDSID and MailboxUploaded are unique.

You can check if the mapping table is working by copying and pasting lines 44-47, renaming the table and taking away the MAPPING keyword.

Jonathan