How to get value based on existence of record

[#1]

I have three data sources. The first two are orders, and I'm using concatenate to create a single table (named "Orders") which is working fine. One of the two order data sources has a field named "order_status". The value of this field needs to be populated based on whether a record exists in the third data source with the common/lookup key being the order number.

So, for example, if the order number is 12345, and a record exists in my third data source for order 12345, the status should be "Filled". If there is no record in the third data source with number 12345, then the status should be "Open". What's the best way to populate the status in the load script? Or is there a different approach I should be using?

[swuehl · Accepted solution]

Maybe create a mapping table from your third table first, like

MAP:

MAPPING LOAD

OrderNo, 'Filled'

FROM ThirdTable;

Orders:

LOAD

OrderNo,

ApplyMap('MAP', OrderNo, 'Open') as OrderStatus

FROM FirstSecondTable;

[#3]

I just found an example using ApplyMap as your reply came in and I was thinking "I think this is what I need", and your reply verified I was going in the right direction. Half the time searching for an answer is the hard part because you don't know what  you're searching for!