Peek Value from another table based on closest date (FIFO)

[girish_talele]

Dear Experts,

I have two data tables, one for Main Material & second for Sub Material as sown in below image.

As shown, requirement date of first two orders of main material is same i.e. 15/04/2014

Only one order is created for Sub Material (accumulation of orders) with Delivery date 10/04/2014 (5 days less than req date).

Now issue is - I have to link / display the Main Material’s Order no. in Sub material’s table (as shown below).

After trying applymap, peek, lookup functions I failed to get the required results.

Is there any function / method to achieve required result.

Thanks in advance,

Girish.

[MK_QSL]

What is the common field between two tables?

May be IntervalMatch can do the trick..

[girish_talele]

Dear Manish,

Thanks for looking into, there will not be a common field between these two tables except the Main Material will appear as a "Requirement From Material" in Sub Material Table as shown below,

The data is taken from ERP (Make to Stock scenario) & against requirement of Main Material a order proposals are being created for Sub material.

We need to link those tables on basis of Requirement Date & Delivery Date on FIFO (First-In First-Out) basis.

Even Order by will not work as there may be a case where the Requirement date of Main Material are different (e.g. 15/04/2014 & 16/04/2014) but ERP/user may club these two requirement in one order of Sub material with Delivery date as 10/04/2014.

Please suggest.

Girish.

[girish_talele]

Dear Experts,

Is there any way to suffice the requirement.

Girish.