Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

First In First Out remaining quantity calculation?

AkisM
10 - Fireball

My data looks like this

 

DateTransactionQuantitySecurity 
01/01/2018Buy100000Apple 
01/02/2018Buy200000Apple 
01/05/2018Sell50000Apple 
05/02/2018Buy200000Banana 
05/03/2018Buy200000Banana 
13/03/2018Sell180000Banana 
17/03/2018Sell50000Banana 

 

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

 

DateTransactionQuantitySecurityRemaining  
01/01/2018Buy100000Apple50000  
01/02/2018Buy200000Apple200000  
01/05/2018Sell50000Apple   
05/02/2018Buy200000Banana0  
05/03/2018Buy200000Banana170000  
13/03/2018Sell180000Banana   
17/03/2018Sell50000Banana   

 

Appreciate your assistance in advance.

8 REPLIES 8
DavidP
17 - Castor
17 - Castor

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
10 - Fireball

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
17 - Castor
17 - Castor

Hi @AkisM 

 

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

 

DavidP_0-1597831106888.png

 

AkisM
10 - Fireball

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
17 - Castor
17 - Castor

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.

 

DavidP_0-1597835799004.png

 

AkisM
10 - Fireball

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
17 - Castor
17 - Castor

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
10 - Fireball

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!

Labels