Use Peek/Previous from 2 tables

[markgraham123]

Hi All -

I have 2 diffferent tables which i cannot join into one table due to my data structure.

Table1
Item	Units
A	10
B	20
C	30
D	40
E	50

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	10
2/14/2018	ABC	B	20
2/15/2018	DEF	A	10
2/15/2018	GHI	C	30

Req O/P:
OrderDate	Order	Item	Req	Flag
2/14/2018	ABC	A	10	Y
2/14/2018	ABC	B	20	Y
2/15/2018	DEF	A	10	N
2/15/2018	GHI	C	30	Y

I wanted to start allocating my units by oldest order date first.

Once allocated, i wanna subtract the allocated units from units it doesnot get allocated in a duplicate way.

Once the units cannot fill 'req' field, 'N' will populate, else 'Y'

Can someone please help??

Please note that i just want to lookup the value from Table1, but not join it.

Wanted to do this in script.

Any help is highly appreciated.

[sunny_talwar]

So, if the data was something like this

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	5
2/15/2018	GHI	C	30

The output would have been this?

OrderDate	Order	Item	Req	Flag
2/14/2018	ABC	A	10	Y
2/14/2018	ABC	B	20	Y
2/15/2018	DEF	A	10	Y
2/15/2018	GHI	C	30	Y

Also, what would be the output for a scenario like this

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	8
2/15/2018	GHI	C	30

[vikraant]

Hi Mark,

As far as I have understood the problem, what is required is, if total number of units of item in table 2 exceeds that mentioned in table 1 then flag should be 'N' . If the number of units of item in table 2 is less than that in table 1 , then the flag should be 'Y'.  And this should be sorted as per the date.

Try out the following logic :

//------------------------------------------------------------------------------------------------------------------------------

//Assuming one to one mapping for units

Table1 :

Mapping Load * Inline

[

Item,	Units
A,	10
B,	20
C,	30
D,	40
E,	50

];

[Req O/P]:

Load

    OrderDate,

   Order,

    Item,

    Req ,

  if( Item = Peek(Item)  , Peek(Req_Cumm) +  Req ,  Req ) as Req_Cumm,

  if( Req_Cumm  >  ApplyMap('Table1', Item, 0), 'N', 'Y')   as Flag

Resident Table2  Order By OrderDate, Order, Item ;

Drop Table Table2;

//------------------------------------------------------------------------------------------------------------------------------

Best Regards,

Vikraant

[Peter_Cammaert]

Order Fulfillment?

Joining your stock to those Order rows is pretty much the easiest way to get to your end-result. Keep in mind that in the end such a solution wouldn't force you to keep any superfluous fields in your order table. Just the Flag field.

As possible methodology:

• Create a mapping table that maps each product ot its stock level.
• Take your orders table, and order it by product and date. Use an ORDER BY clause when performing the next two steps. ORDER BY happens before anything else.
• At the same time, whenever the previous product is different from the current one, lookup the initial stock value using the mapping table and set Flag=Y if you have enough stock and Flag=N if the initial stock cannot fulfill the requested amount. Store the remaining stock in a new column.
• If the current product is the same as the previous product, compare the remaining stock of the previous row to the ordered amount. Set Flag=Y if you still have enough stock and Flag=N if the previous stock cannot fulfill the requested amount. Store the remaining stock in a new column.

Now remove the Remaining stock column and reorder your table as you like. IS that possible in your data model?

Peter

[markgraham123]

Your output example is correct.

For the scenario you have provided, output will be as follows:

Table2
OrderDate		Order	Item	Req  Flag
2/14/2018		ABC	A	5         Y
2/14/2018		ABC	B	20       Y
2/15/2018		DEF	A	8         N
2/15/2018		GHI	C	30       Y

[Peter_Cammaert]

Another interesting variation on Sunny's example. What would be the Flag value for the last line:

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	8
2/15/2018	GHI	C	30
2/16/2018	XYZ	A	3

Is the Req=8 line taking those last 5 pieces (partial delivery)?

[markgraham123]

Yes sir.

As it now has only 5 units left after allocating units to first order, it says N for another order.

[Peter_Cammaert]

Like this?

(I'm posting this at the end, because this example does what the previous post tries to explain)