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]

I am not sure what the best way would be, because the best way depends on your total requirements.

One way would be to calculate the Total QTY in the script, maybe like

SET DateFormat = 'M/D/YYYY';

Let vBeginBalance =30;

LOAD *, Rangesum(if(recno()=1,$(vBeginBalance),peek(TotalQTY)),DiffQTY) as TotalQTY;

LOAD *, if(TransType='Receiving',QTY,-QTY) as DiffQTY;

LOAD * INLINE [

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

];

You will notice a small difference to your numbers, but I think QV is doing the math quite well..

[maxgro]

SET DateFormat='MM/DD/YYYY';

source:

load 30 as BeginningBalance AutoGenerate 1;

join (source)

LOAD * INLINE [

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

];

final:

load

  *, 

  if(peek([Item Number])=[Item Number],

  peek(Total_QTY) + QTY * if(TransType='Receiving',1,-1),

  BeginningBalance + QTY * if(TransType='Receiving',1,-1)

  ) as Total_QTY

Resident source

order by TransDate;

DROP field BeginningBalance;

DROP Table source;

[#4]

The requirement is to select a specific date.  Upon selecting that date we want a list of all of our Inventory Items, QTY, Cost per Item, and Over-all Price of Inventory on hand.  So the only way I thought was to start with the beginning balance of all the items.  Pick a date.  Once the date is selected add all the receiving transactions through that date and add it beginning balance.  Then take that number and subtract the total number of issue transactions to get your on hand qty on the date you selected.  You would need to do it on a item by item basis and then run some totals.  Does this make sense?

[swuehl]

Sure, makes sense.

One question is if you need to calculate the total qty on hand selection specific or if this is somewhat static (what I assume right now).

If it's static, you can calculate it in the script, similar to what I shown above (essentially, create an input table ordered by Item and TransDate, then use peek() function to access values from the previous output table record (for the accumulation over time) and to check for item change (like Massimo showed in his post).

[#6]

The problem I see is that each item will have a different beginning balance.  What I would like to do is to put a variable in as the date for example 2/14/2012.  From there I would like the following:

Item Number     Total QTY on Hand      Total Value on Hand

The only way I see to do it is to take the beginning balance of each item.  Add all the receiving transactions.  Subtract all the issue transactions.  Once that is done you can get your on hand qty.  I am not sure how to accomplish this with the example above since it shows beginning balance of 30.  I am sure there will need to be some grouping done.

[swuehl]

Right, just create a table in your load with the beginning balances, then either join or look up the values when needed:

SET DateFormat = 'M/D/YYYY';

BeginBalanceTable:

LOAD * INLINE [

ItemNumber, BeginBalance

123,30

124,15

];

Transactions:

LOAD * INLINE [

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

124, 1/12/2012, Issue, 10, 25

124, 1/23/2012, Receiving, 5, 30

124, 1/31/2012, Receiving, 20, 50

124, 2/5/2012, Issue, 5, 45,

124, 2/8/2012, Issue, 15, 20,

124, 2/14/2012, Issue, 5, 25,

124, 3/1/2012, Receiving, 20, 55

124, 3/15/2012, Receiving, 10, 65

124, 3/30/2012, Issue, 5, 60

];

LOAD *,

  Rangesum(

  if(peek([Item Number]) <> [Item Number],

  lookup('BeginBalance', 'ItemNumber', [Item Number], 'BeginBalanceTable'),

  peek(TotalQTY))

  ,DiffQTY) as TotalQTY;

LOAD *, if(TransType='Receiving',QTY,-QTY) as DiffQTY

Resident Transactions

order by [Item Number], TransDate;

drop table Transactions, BeginBalanceTable;

[#8]

Ah.  That makes sense.  I am still new to Qlikview and like this community because you all have been helpful.  The only question I have is will I need to do something similar with the issue transaction type?  Since issue decrease the inventory and receiving increase inventory. 

[swuehl]

Won't  this line do the trick regarding the decreasing and increasing inventory?

if(TransType='Receiving',QTY,-QTY) as DiffQTY

Have you check the result of the test script?

[#10]

Ok.  I need to explain this a little further.  The receiving and issue transactions are in the same table. The Total_QTY_onHand I did the math for demo purposes.  It is actually something that will need to be calculated.  The items do have an entry for beginning balance as well.  Does this change things?