Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

  • );
  • Tags (2)
    1 Solution

    Accepted Solutions
    Not applicable

    Re: Create variable filename, and load from variable

    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 Replies

    Re: Create variable filename, and load from variable

    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

    Re: Create variable filename, and load from variable

    MaxFolder is a field i calculated in a prior table

    Re: Create variable filename, and load from variable

    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

    Re: Create variable filename, and load from variable

    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

    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

    Re: Create variable filename, and load from variable

    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

    Re: Create variable filename, and load from variable

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

    danrider63
    New Contributor

    Re: Create variable filename, and load from variable

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

    Community Browser