Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I was wondering if anyone has done any Inventory Reconciliation Applications that can share how they did it. Here is an example of my data set:
Beginning Balance | 30 | |||
Item Number | TransDate | TransType | QTY | Total_QTY_onHand |
123 | 1/1/2012 | Receiving | 5 | 35 |
123 | 1/12/2012 | Issue | 10 | 25 |
123 | 1/23/2012 | Receiving | 5 | 30 |
123 | 1/31/2012 | Receiving | 20 | 50 |
123 | 2/5/2012 | Issue | 5 | 45 |
123 | 2/8/2012 | Issue | 15 | 20 |
123 | 2/14/2012 | Issue | 5 | 25 |
123 | 2/28/2012 | Receiving | 10 | 35 |
123 | 3/1/2012 | Receiving | 20 | 55 |
123 | 3/15/2012 | Receiving | 10 | 65 |
123 | 3/23/2012 | Issue | 5 | 60 |
123 | 3/30/2012 | Issue | 5 | 60 |
I have done the math for demonstration purposes.but Recieving's increase and Issues decrease my inventory qty. What I would like to do is have a date variable, lets say for demo purposes 2/8/2012. The Total_QTY_onhand for the above example would be 20. I am trying to determine the best way to accomplish this and I am open to suggestions. Let me know if there are any questions.
David
Understood. I just left in this field for final comparison with the script calculated field TotalQTY...
So you can just remove the field Total_QTY_onHand from the load script.
I am working on this now. It completely makes sense what you have above. How would you add additional transaction types to the model. For example:
BeginBalanceTable:
LOAD * INLINE [
ItemNumber, BeginBalance
123,30
];
Transactions:
LOAD * INLINE [
Item Number, TransDate, TransType, QTY,
123, 1/1/2012, Receiving, 5
123, 1/8/2012, Adjustment, -2
123, 1/12/2012, Issue, 10
123 , 1/22,2012 , Return, 5
123, 1/23/2012, Receiving, 5
123, 1/31/2012, Receiving, 20
123, 2/5/2012, Issue, 5
123, 2/8/2012, Issue, 15
123, 2/14/2012, Issue, 5
123, 2/28/2012, Receiving, 10
123, 3/1/2012, Receiving, 20
123, 3/15/2012, Receiving, 10
123, 3/18/2012, Adjustment, 2
123, 3/23/2012, Issue, 5
123, 3/30/2012, Issue, 5
So Receiving and Returns will increase my inventory, adjustments could increase or decrease my inventory, and issues would decrease my inventory.
Hi there try this
Load *,
if(Peek(Item)=Item,rangesum(peek(Closingstock),if(TransType='Receiving' or TransType='Adjustment' or TransType='Return' ,QTY,(-1)*QTY),
if(TransType='Receiving' or TransType='Adjustment' or TransType='Return' ,QTY,(-1)*QTY)) as ClosingStock,
resident Orginal
order by Item,Date asc;
Just expand the above expression to something like
pick(match(TransType,'Receiving','Return','Adjustments','Issue'),1,1,1,-1) * QTY as DiffQTY
Which expression are you talking about?
The one in the Load script that defines DiffQTY.