First In First Out remaining quantity calculation?

[AkisM]

My data looks like this

Date	Transaction	Quantity	Security
01/01/2018	Buy	100000	Apple
01/02/2018	Buy	200000	Apple
01/05/2018	Sell	50000	Apple
05/02/2018	Buy	200000	Banana
05/03/2018	Buy	200000	Banana
13/03/2018	Sell	180000	Banana
17/03/2018	Sell	50000	Banana

and after applying the first in first out method, I would like to figure out the remaining quantities, and thus the data should look something like this

Date	Transaction	Quantity	Security	Remaining
01/01/2018	Buy	100000	Apple	50000
01/02/2018	Buy	200000	Apple	200000
01/05/2018	Sell	50000	Apple
05/02/2018	Buy	200000	Banana	0
05/03/2018	Buy	200000	Banana	170000
13/03/2018	Sell	180000	Banana
17/03/2018	Sell	50000	Banana

Appreciate your assistance in advance.

[DavidP]

Hi @AkisM 

This is a very similar problem to the below post. Please look at the 2 solutions on page 3

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-product...

Let me know if you have any questions.

[AkisM]

Hi @DavidP , thanks for your post. I went through all pages of the thread you mentioned before posting. In fact it was your solution that I downloaded and wanted to try first since it didn't use an iterative macro (which is a bit outside my knowledge at the moment).

The issue is that that thread calculates something else (price according to FIFO). I'm not looking for price, I'm looking for remaining quantity. I downloaded your proposed solution that wasn't using an iterative macro from that post, but unless I'm missing something it wasn't giving me what I needed (remaining quantities after applying FIFO), it was giving price.

[DavidP]

Hi @AkisM 

Here's an amended version of my Fifo workflow for your example

[AkisM]

Thanks @DavidP for the elegant solution. Works like a charm. The only issue is that with my data... Each generate row tool generates about 22GB of data lol. Which makes the workflow quite difficult/time consuming to run. Is there a workaround? If the only way to get past that is using an iterative macro then I'm fine with it.

[DavidP · Accepted solution]

Ouch! @AkisM  - that's a lot of rows!

There are 2 things I can think of:

1: Remove all unnecessary fields both before and after to Generate Rows and also in the join tool. You can see in the updated version attached where I've removed unnecessary fields. Also, I changed the logic in the filter tool to use Record ID instead of Date, so that Date can be removed as well. For the purpose of calculating the value for [Count] in the Summarize tool, everything that is not shown in this flow should be removed. All this might only moderately reduce the size of the dataset, but it should have some improvement.

2.  The biggest impact is due to the number of rows generated. The only way to reduce this is to set a minimum Buy and Sell quantity. Currently the Generate Rows reduces each Buy and Sell event to rows with Quantity of 1. By having a minimum sell quantity of 10 for instance, you'll get a factor of 10 improvement in each Generate rows tool which should give you at least a 20% improvement. The changes required to implement this is to change the Loop expression in each Generate Rows tool and in the final formula tool where you have to multiply [Count] with the minimum buy/sell quantity as shown in example 2 attached.

[AkisM]

Thanks @DavidP , that really makes the workflow way quicker. I just noticed something else that was wrong though. At the input stage, you included the column "remaining". That column should actually be a calculated field, as it is not included in the input data. I just put it there as an example of what the output data should look like.

Any ideas how we can make that a calculated field instead?

[DavidP]

Hi @AkisM 

You'll see that I actually removed the [Remaining] field from the input in my last 2 examples. I only kept it in initially to test if I get the correct result and it's not used anywhere in the workflow. You'll notice in the last formula tool that the calculated field is called [Calculated Remaining] - You can remove Calculated from the name here.

[AkisM]

You are absolutely right @DavidP , apologies, I got confused along the way, as my data actually had negative quantities for sells (my bad for not reflecting it in the input example i provided), which interfered with the result. Used absolute values instead and it works perfectly. Thank you very much for your time and for sharing your knowledge!