Load Two Most Recent Files

Anonymous — Mon, 29 Feb 2016 20:53:35 GMT

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?

Any help would be greatly appreciated. Thank you!

Re: Load Two Most Recent Files

swuehl — Mon, 29 Feb 2016 22:22:20 GMT

You can assign variables like

Let vMaxFileDate = Peek('MaxFileDate',0,'Table2');

Let vMax2FileDate = Peek('Max2FileDate',0,'Table2');

Then use the variables in your FROM statement like

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Load Two Most Recent Files

Anonymous — Mon, 29 Feb 2016 22:36:15 GMT

Awesome. That's exactly what I needed. Thank you so much!

topic Load Two Most Recent Files in QlikView

Load Two Most Recent Files

Re: Load Two Most Recent Files

Re: Load Two Most Recent Files