Maintaining a History data

[Lumjing]

Hi I have a daily file which gets updated with daily sales. I want to have a workflow which will keep daily sales data till the last day of the month and when the month changes to Feb the data should append please see below tables example. 

Daily Sales during a month ( From another input file)

Sales Amount	sales date	Month
1234	2/5/2020	May

The above May file data should append to Historical file when workflow is ran on 1st May (I am maintaining separate historical file) 

Sales Amount	sales date	Month
1234	1/1/2020	Jan
1234	3/1/2020	Jan
1234	31/1/2020	Jan
2345	2/2/2020	Feb
2345	8/2/2020	Feb
2345	9/2/2020	Feb
2345	28/2/2020	Feb
3456	1/3/2020	March
3456	2/3/2020	March
3456	3/3/2020	March
3456	30/3/2020	March
1234	9/4/2020	April
1234	2/4/2020	April
1234	29/4/2020	April

[BrandonB]

Is your daily file the cumulative rows for the given month? Does this file lose the past month's data when the month changes? A little more information about how these files change would help before we give a recommendation. I am trying to determine if these files are "monthly snapshots" or if you are trying to capture a point in time because you don't have access to this data in the following month. 

[Lumjing]

@BrandonB 

Is your daily file the cumulative rows for the given month?

- I have a daily file that keeps updating sometime cumulative rows and sometimes just the data update.  

Does this file lose the past month's data when the month changes?

- yes but not the last working day of the month. When the month changes I should be able to save the last working day data of that month like for november 2020 I should be able to save the 30th nov data and then when I run the workflow the next day 1st December the data should be appended to 30th nov file.

let me know if you require any further details.

[BrandonB]

Is the group that creates this file internal to your company? It may be a lot easier to just ask that in addition to the one file that gets updated, they also have a separate extract that puts the complete months in the folder as separate files. You can definitely do this in Alteryx, but I just figured that would be the more efficient approach. If you want to do this in a workflow that you have scheduled to run daily you will need some logic that looks at the current day compared to the last day of the month and either always write a file of that current month (basically making a copy and overwriting that copy with more of the current months data), or have it append only if those two dates match up:

Then you also need to think about the Excel file size that will be generated. It sounds like having one file for each separate month may be the best approach here to reduce complexity and avoid large file sizes.