Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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
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:
Now remove the Remaining stock column and reorder your table as you like. IS that possible in your data model?
Peter
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 |
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)?
Yes sir.
As it now has only 5 units left after allocating units to first order, it says N for another order.
Like this?
(I'm posting this at the end, because this example does what the previous post tries to explain)