topic Re: Creating a FIFO Stock Table in QlikView

Creating a FIFO Stock Table

Thu, 20 Aug 2015 08:59:35 GMT

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.

Re: Creating a FIFO Stock Table

jonas_rezende — Thu, 20 Aug 2015 18:06:57 GMT

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.

Re: Creating a FIFO Stock Table

Fri, 21 Aug 2015 07:07:13 GMT

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.

Re: Creating a FIFO Stock Table

jonas_rezende — Fri, 21 Aug 2015 18:25:25 GMT

Hi, Adam.

How about?

Regards,

Jonas Melo.

Re: Creating a FIFO Stock Table

Fri, 21 Aug 2015 21:03:39 GMT

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.

Re: Creating a FIFO Stock Table

Fri, 21 Aug 2015 21:04:49 GMT

Attached is the colouur co ordinated explanation