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