Creating a FIFO Stock Table

[#1]

Hi

I am fairly new to Qlikview but need to create a table by joining two sets of data and am not sure how to do this. I will try my best to explain.

I have a table of Purchase Transactions and a Table of Sales Transactions, each table has a date, stock code, qty and document number, what I need to do is match up the Purchase invoice with the corresponding Sales Invoice for each stock item. Effectively we will buy an item of stock in a batch the batch can either be sold in one e.g. 25 in and 25 out or in smaller quantities e.g. 25 in then 5 lots of 5 out however the data does not have a common reference to match by.

We use the FIFO (First in First Out) Principal for our stock.

I have attached an Excel document with an example of the 2 tables and what I am trying to get to. This is a small extract I have over 2000 stock items and over 300,000 transactions.

Thank you in advance for your help.

[jonas_rezende]

Hi, Adam.

See if qvw helps! Otherwise, hope give a good idea for you.

Note: each tab of script, including Straight Table in  presentation layer.

Regards,

Jonas Melo.

[#3]

Hi Jonas

Thanks for your answer but unfortunately this is not quite what I am looking for.

What I need is a single report which has the in and out on one line so that I can see for the sales invoice what purchase invoice it came in on.

[jonas_rezende]

Hi, Adam.

How about?

Regards,

Jonas Melo.

[#5]

Hi Jonas

Thank you again but this is not quite right.

The issue is that I need to match up every movement in against the corresponding out and this could be either an exact match e.g 50 in and then 50 out, a single in being split accross multiple outs e.g. 50 in then 25 out and another 25 out or it could be multiple in's and a single out e.g. 25 in and another 25 in the 50 out.

I have updated the excel sheet and colour co ordinated it to illustrate exactly what is needed and added notes to explain.

I appreciate your help.

[#6]

Attached is the colouur co ordinated explanation