topic Create variable filename, and load from variable in QlikView

Create variable filename, and load from variable

Fri, 16 Jan 2015 17:12:10 GMT

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

);

Re: Create variable filename, and load from variable

Colin-Albert — Fri, 16 Jan 2015 17:23:23 GMT

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

);

Re: Create variable filename, and load from variable

Fri, 16 Jan 2015 17:27:17 GMT

MaxFolder is a field i calculated in a prior table

Re: Create variable filename, and load from variable

Gysbert_Wassenaar — Fri, 16 Jan 2015 17:28:44 GMT

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;

Re: Create variable filename, and load from variable

Fri, 16 Jan 2015 18:37:38 GMT

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

Re: Create variable filename, and load from variable

Gysbert_Wassenaar — Fri, 16 Jan 2015 18:46:31 GMT

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

Re: Create variable filename, and load from variable

Fri, 16 Jan 2015 18:50:12 GMT

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)

Re: Create variable filename, and load from variable

Fri, 16 Jan 2015 18:50:32 GMT

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

Re: Create variable filename, and load from variable

Anonymous — Tue, 17 Apr 2018 14:17:27 GMT

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