Historical Data Comparison with Formula

[Mike_at_CG]

Hello Alteryx Community :)

Hoping I can get a little help on the below file comparison.

Background for reference:

1.  Filling file will always have a date after the Position File.

2.  Both files will always have different headers

Trying to create a formula or use a tool in Alteryx to compare the current Filling File with a historical Position File and update the current Filling File based on matching Station, Action and quantity.

Position File below

Filling File below

I am thinking Alteryx will need to go and find the position file 1 day before the date on the filling file, match the station and then based on the action and quantity make a decision.

So for example with Wawa the Filling File on the 15th is 20 which is 5 less than the Position File on the 14th therefore it is a trim.  BP went from 0 to 5 which is a new purchase.

I was able to make a very long function in Excel but was hoping to add this to a flow in Alteryx that is connected to other parts of our business.

Thank you

[ARussell34]

Try this! If its always one day difference than you can use a formula with date time difference to find 1 day apart and only use the stations that joined between the two files.

[Mike_at_CG]

Thank you for this.  Can you confirm if this would work for a larger data set?  Meaning the position file will actually be from 2016 until today and every new day.  The filling file will also be from 2016 until today and then every new day after.  

So for example in the full data set WAY back in 2016 there was a position file for 11/14/2016 and then on the 15th there was a new filling file.  Would this work to fill in all of those dates as well?

Thank you again for the help on this!

[wdavis]

Hi @Mike_at_CG 

Yes, in the example above this would work for data going back to 2016. However, as it is matching just on the Join it would cause there to be quite a large number of records joining.

Therefore, in the process I have added a Formula tool to the Filing File which creates a new field 'DateMatch' which  subtracts 1 day from the date. In the Join you are then able to match on both 'Station' and 'Match Date', which will reduce the number of records that are joined together.

The remaining logic can then remain the same. 

Let me know if this works!

Will