Using variables in the FROM clause of a LOAD statement

haymarketpaul — Fri, 05 Feb 2016 09:58:04 GMT

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?

Re: Using variables in the FROM clause of a LOAD statement

haymarketpaul — Fri, 05 Feb 2016 10:57:58 GMT

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);

topic Re: Using variables in the FROM clause of a LOAD statement in QlikView

Using variables in the FROM clause of a LOAD statement

Re: Using variables in the FROM clause of a LOAD statement