Create variable filename, and load from variable

[#1]

Howdy,

I'm trying to create a variable in the load script, and use it as PART of the file name to load from.

Why is my vNewestFolder working in the qvw itself, but not passing the value into the file name? I'm just trying to slip that variable in as part of my filepath

set vNewestFolder = max(MaxFolder);

set vFileName = 'D:\Apps2\CUSTOMER RELATIONS\VendorOrderStatus_Emails\' & $(vNewestFolder) & '*';

DataLoad:

LOAD *

FROM

[$(vFileName)]

(ooxml, embedded labels, table is

);

[Colin-Albert]

You could add a trace on the variables to see what values they are being set with.

Is there a "v" missing from MaxFolder, should it be vMaxFolder?

set vNewestFolder = max(MaxFolder);

trace vNewestFolder = $(vNewestFolder) ;

set vFileName = 'D:\Apps2\CUSTOMER RELATIONS\VendorOrderStatus_Emails\' & $(vNewestFolder) & '*';

trace vFileName = $(vFileName ) ;

DataLoad:

LOAD *

FROM

[$(vFileName)]

(ooxml, embedded labels, table is

);

[#3]

MaxFolder is a field i calculated in a prior table

[Gysbert_Wassenaar]

set vNewestFolder = max(MaxFolder); will set the variable vNewestFolder to the string value 'max(MaxFolder)'. That's probably not what you want. If you have a table with a field MaxFolder you can retrieve the max value of that like this:

Temp:

LOAD max(MaxFolder) as MaxMaxFolder

RESIDENT ...table_containing_maxfolder...;

LET vNewestFolder = peek('MaxMaxFolder');

DROP TABLE Temp;

[#5]

That worked on letting me use the right vNewestFolder!!

Thank you,

Now, how do i integrate that in my filepath?

From

this isn't working

[Gysbert_Wassenaar]

Works for me. But table is

will not work. If you want to load all the sheets from an excel file you'll have to use a different approach using odbc and the sqltables function. See this discussion for an example: http://community.qlik.com/message/546602#546602

[#7 · Accepted solution]

Thanks for your help guys!

//Script to remove all preceding and post fluff from the folder name - resulting in a YYYYMMDD value

//Preceding load grabs the most recent value

MaxFolder:

Load

max(SourceFile) as MaxFolder;

Load

Mid(SourceFile, index(SourceFile,'Emails')+7,index(SourceFile,'\2')-index(SourceFile,'Emails')+2) as SourceFile

Resident FileList;

//Peek the most recent value, MaxFolder

Let vNewestFolder = peek('MaxFolder');

//Load all *.xlsx files from the folder that matches the vMaxFolder = YYYYMMDD that is most recent

DataLoad:

LOAD *

FROM

(ooxml, embedded labels)

[#8]

I ended up removing the tab code, as I always want to load the first one

[Anonymous]

Just as a note the variable that you "Let" for the file path can NOT include the [square brackets] in the syntex.