Inventory Reconciliation

[#1]

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

[swuehl]

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.

[#3]

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. 

[preminqlik]

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;

[swuehl]

Just expand the above expression to something like

pick(match(TransType,'Receiving','Return','Adjustments','Issue'),1,1,1,-1) * QTY as DiffQTY

[#6]

Which expression are you talking about?

[swuehl]

The one in the Load script that defines DiffQTY.