Forum Discussion
Week Over Week comparison using daily files in the same folder
I'm building a query that accesses a folder where daily files are saved. I need to compare the most recent file to the file from the same day last week (the 6th file from the top of folder). The run date is a column in the data files as well as embedded in the file name. How do I best set up the query to access only the data from the 1st and 6th files, and then compare certain measures by line from these 2 files?
Here's sample data:
From today's file (top row of the folder):
Report Run Date EventSiteId Actual Total $
2/22/2023 0:00 1223 41202.56
From one week ago file (6th file from the top of the folder):
Report Run Date EventSiteId Actual Total $
2/15/2023 0:00 1223 30653.45
Restating the above question using this example: Based on EventSiteID, how can I compare today's actual to one week ago actual using data from these 2 files?
Hi klarrick ,
if it is always the 6th file, you could add an index column once you have imported from folder.
If it is 0-based, choose values 0 and 5 from that index column. That leaves you with the relevant files.
Expand out the file content, check column "Report Run Date" and delete it. Then check the Index-column and pivot on it with column "Actual Total $" as amount-column.
This will return 2 new columns: 0 and 5: Check them and add column that subtracts one from the other.
If you want to keep the a reference day in your table, add it as an additional column after you have created your index with the following formula: if [Index] = 0 then [Report Run Date] else null
Then fill down on that column.
1 Reply
Hi klarrick ,
if it is always the 6th file, you could add an index column once you have imported from folder.
If it is 0-based, choose values 0 and 5 from that index column. That leaves you with the relevant files.
Expand out the file content, check column "Report Run Date" and delete it. Then check the Index-column and pivot on it with column "Actual Total $" as amount-column.
This will return 2 new columns: 0 and 5: Check them and add column that subtracts one from the other.
If you want to keep the a reference day in your table, add it as an additional column after you have created your index with the following formula: if [Index] = 0 then [Report Run Date] else null
Then fill down on that column.