Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
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);