I have a file that is added to a drive on my computer every day containing spending information. I am supposed to compare those values by Part Number and their changes day over day. The file name has the date embedded in it. Therefore, in the script, I use the following function to identify the file date:
date(date#(mid(filename(),30,8),'YYYYMMDD')) as file_date
I need to load the two most recent files, ie the max file date file and the second-to-max date file. The most recent file date is not always "Today", and the second most recent file date is not always "Today() - 1", so these phrases in the code will not work.
What I am thinking is that I need to "First 1 LOAD" all of these files, then in a second table identify the max file date and the second to max file date as follows:
LoadAll:
First 1
LOAD
Material,
Description,
[Certification Status],
[Vendor 1],
[Vendor 2],
[Vendor 3],
[Vendor 4],
[Total Vendors],
Buyer,
round(Calc,'0.001') as Calc,
round(Calc1,'0.001') as Calc1,
round(Calc2, '0.001') as Calc2,
if(1=1,1,1) as Field,
date(date#(mid(filename(),30,8),'YYYYMMDD')) as file_date
FROM
(ooxml, embedded labels, table is Sheet1);
Table2:
NoConcatenate Load
Field,
date(max(file_date)) as MaxFileDate,
date(max(file_date,2)) as Max2FileDate
Resident LoadAll
Group by Field;
Now I think what I need to figure out is how to load the file where the file date = MaxFileDate and then do some mapping loads and load the file where file date = Max2FileDate, and then compare there fields between the two by setting them next to each other by applying the mapping loads in the Max2FileDate load. I can do this as long as I can figure out how to load the MaxFileDate and Max2FileDate files. Is there a way to assign these values to a variable to use them in the file name in the FROM clause? Any ideas?
