I am pulling a sales & labor file everyday and trying to append all csv files in one query. The issue is I pull this with a 7 day lookback and need the new data to overwirte the old data. I cannot do a groupby as labor will sometimes be adjusted during the validation process and the labor hours will be different between the old and the new data. I currently have daily data starting 1/1/2021 and need to overwrite the last 7 days of data everyday, so my timeframe is constantly chaning.
Since all csv files are in one folder, my current thought process is to load the folder into two queries. On one query, called "Historical data", I will load the folder, expand to show the tables, then apply a filter to remove the last 7 days of data. On the second query, called "New Data", I will filter out the files to show only the most recent file (the file that I uploaded that day), then expand it to show the table. I then append New Data to Historical Data.
Is this the most efficient way to do this, or is there some sort of recurssive loop that I can use to overwrite historical data with new data in one query? I was unable to find anything on google about this, but it seems this would be a common problem with any kind of data that is updated daily.
Thanks,
Reed
