Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Inventory Reconciliation

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 NumberTransDateTransTypeQTYTotal_QTY_onHand
1231/1/2012Receiving535
1231/12/2012Issue1025
1231/23/2012Receiving530
1231/31/2012Receiving2050
1232/5/2012Issue545
1232/8/2012Issue1520
1232/14/2012Issue525
1232/28/2012Receiving1035
1233/1/2012Receiving2055
1233/15/2012Receiving1065
1233/23/2012Issue560
1233/30/2012Issue560

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

15 Replies
MVP
MVP

Re: Inventory Reconciliation

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..

MVP
MVP

Re: Inventory Reconciliation

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;

Not applicable

Re: Inventory Reconciliation

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?

MVP
MVP

Re: Inventory Reconciliation

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).

Not applicable

Re: Inventory Reconciliation

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.

MVP
MVP

Re: Inventory Reconciliation

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;

Not applicable

Re: Inventory Reconciliation

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. 

MVP
MVP

Re: Inventory Reconciliation

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?

Not applicable

Re: Inventory Reconciliation

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?

Community Browser