Using variables in the FROM clause of a LOAD statement

[haymarketpaul]

QV11 SR4

Can anyone tell me how to use variables in the FROM clause of a LOAD statement?

I have the following code which works fine except for the last LOAD statement as i'm not quite sure on the syntax for using variables in the FROM clause of a LOAD statement...

//Hammstore Server

LET vHammstore = 'HAMMSTORE';

//Path to Hammstore

LET vHammstorePath = '\\' & '$(vHammstore)' & '\Direct\Circulation\Data Ops\Medical\MIMS Learning - Binleys\';

//Get the latest filename in the folder

FOR each File in filelist $(vHammstorePath)MIMs Learning - Matched Records*.*

Folder:

LOAD

    '$(File)' as Name,

    FileTime( '$(File)' ) as FileTime

    autogenerate 1;

NEXT File

LatestFile:

First 1

LOAD

    Name,

    SubField(Name,'\',SubStringCount(Name,'\')+1)    as FileNameOnly,

    FileTime,

    1 as dummy

Resident Folder

Order By FileTime DESC;

DROP Table Folder;

LET vLatestMIMSFile = Peek('FileNameOnly',0,'LatestFile');

LET vLatestMIMSFileWithPath = Peek('Name',0,'LatestFile');

DROP Table LatestFile;

SPOP_MIMs_Learning:

LOAD

    *

FROM

$(vHammstorePath)$(vLatestMIMSFile);       // <<<<< what is the syntax needed here?

[haymarketpaul · Accepted solution]

don't worry i worked it out...

//Hammstore Server

LET vHammstore = 'HAMMSTORE';

//Path to Hammstore

LET vHammstorePath = '\\' & '$(vHammstore)' & '\Direct\Circulation\Data Ops\Medical\MIMS Learning - Binleys\';

//Get the latest filename in the folder

FOR each File in filelist $(vHammstorePath)MIMs Learning - Matched Records*.*

Folder:

LOAD

    '$(File)' as Name,

    FileTime( '$(File)' ) as FileTime

    autogenerate 1;

NEXT File

LatestMatchingFile:

First 1

LOAD

    Name,

    SubField(Name,'\',SubStringCount(Name,'\')+1)    as FileNameOnly,

    FileTime,

    1 as dummy

Resident Folder

Order By FileTime DESC;

DROP Table Folder;

LET vLatestMIMSFile = Peek('FileNameOnly',0,'LatestMatchingFile');

LET vLatestMIMSFileWithPath = Peek('Name',0,'LatestMatchingFile');

DROP Table LatestMatchingFile;

//ADD any NEW SPOP MIMS Learning users who do not exist already (based on Email Address)

SPOP_MIMs_Learning:

LOAD

    *

FROM

[$(vLatestMIMSFileWithPath)]

(ooxml, embedded labels, table is Sheet1);