Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create variable filename, and load from variable

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

  • );
  • 1 Solution

    Accepted Solutions
    Not applicable
    Author

    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)

    View solution in original post

    8 Replies
    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

  • );
  • Not applicable
    Author

    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;


    talk is cheap, supply exceeds demand
    Not applicable
    Author

    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


    talk is cheap, supply exceeds demand
    Not applicable
    Author

    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)

    Not applicable
    Author

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

    Anonymous
    Not applicable
    Author

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