Lookup across Tables and Create a new Field Value

[8Gates]

Afternoon - hoping for some help with the following quandary.

I have two tables - Items & TradeAgreement.

On Load, I want to create another field on the Trade Agreement table

#Where the Item number in the Item table does not exist in the Trade Agreement Table

If there is a match - 'Yes' or 'No' as NewFieldName

[Trade Agreements]:
LOAD
[Vend Ac] as [PKVendor Acc],
[Vend Ac] as [TA FKVendor Acc],
[Vend Name],
[Item Id] as [PKItem ID],
[Item Name] as [TA Item Name],
[External Item Id] as [Item ID],
[External Item Name] as [Ext Item Name],
[Invent Dim ID],
[Item Relation],
[Configuration],
[Dim Group ID],
[From Date] as [TA From Date],
[To Date] as [TA To Date],
[Active],
[Unit],
[Quantity] as [Vendor Quantity],
[Price]
FROM [lib://Dev:DataFiles/Vendor Trade Agreement Lines.xls]
(biff, embedded labels, header is 1 lines, table is [Vendor Trade Agreement Lines.rd$]);

[Items]:
LOAD
[Item number] as [PKItem ID],
[Item number] as [FKItem ID],
[Item name],
[Alternative item number],
[Use],
[Vendor] as [Item Vendor],
[Cost centre] as [Item Cost Centre],
[Department] as [Item Department],
[Product],
[Dimension group],
[Item group],
[Item type],
[Stock model group],
[Company item],
[Purchase unit],
[Price] as [Item Price],
[Date of price],
[Stock unit],
[Price2],
[Date of price3],
[Sales unit],
[Price4],
[Date of price5],
[Search name] as [Item Search Name]
FROM [lib://Dev:DataFiles/Item Details.xlsx]
(ooxml, embedded labels, table is [Item Details]);

Having read the posts around Lookup() still unsure.

[8Gates · Accepted solution]

If you are interested - resolved with some code

Build a map table

[Map Items]:
Mapping Load
[Item number],
[Item name]
FROM lib://Dev:DataFiles/Items.QVD (QVD);

Then as the trade agreement load

[Trade Agreements]:
LOAD
[Vend Ac] as [PKVendor Acc],
[Vend Ac] as [TA FKVendor Acc],
[Vend Name],
[Item Id] as [PKItem ID],
If(Len((ApplyMap('Map Items',[Item Id],Null())))< 1,'No','Yes') as [Item Exists],
[Item Name] as [TA Item Name],
[External Item Id] as [Item ID],
[External Item Name] as [Ext Item Name],
[Invent Dim ID],
[Item Relation],
[Configuration],
[Dim Group ID],
[From Date] as [TA From Date],
[To Date] as [TA To Date],
[Active],
[Unit],
[Quantity] as [Vendor Quantity],
[Price]
FROM lib://Dev:DataFiles/Trade_Agreements.QVD (QVD);