Loading From a QVD Where Date is Equal to a Variable - Help!

[#1]

Hi,

I'm trying to use the script below to first load the max date from a table and store it as a variable.  Then to load all results from the table whose date is equal to that max date.  I've checked the date stored by the variable which is '13/05/2016'.  I've also ran a seperate query to return all dates from the table converted to 'DD/MM/YYYY' and '13/05/2016' is one of the results.

Yet when i filter the table using the variable I get no results, even though the date in the variable comes from the same table I'm trying to load from, so they're in the same format! 

Any ideas? 

[MAX ODS Refresh Date]:

load max(Enrol_Download_Datestamp) as Enrol_Download_Datestamp

from [Data\Student Numbers\ODS_Snapshots2.QVD]

(qvd);

let vMaxODSRefresh = DATE(peek('Enrol_Download_Datestamp',0,'[MAX ODS Refresh Date]'),'DD/MM/YYYY');

//Drop Table [MAX ODS Refresh Date];

[Student Numbers]:

LOAD SCJ_KEY,

     DATE(Enrol_Download_Datestamp,'DD/MM/YYYY'),

     Enrol_Report_CRS_Title,

     Enrol_Report_Faculty,

     Enrol_Report_Department,

     Enrol_Report_OnOff_Campus,

     Enrol_Report_HOandEU_or_OS,

     Enrol_Report_HO_or_EU_or_OS,

     Campus_ID,

     Campus_Name,

     FAC_UDF4,

     DupMarker,

     Enrol_Report_Faculty & Enrol_Report_Department & SCE_CRSC as STU_KEY

FROM

[Data\Student Numbers\ODS_Snapshots2.QVD] (qvd)

where DATE(Enrol_Download_Datestamp,'DD/MM/YYYY') = $(vMaxODSRefresh);

[MK_QSL]

use

where DATE(Enrol_Download_Datestamp,'DD/MM/YYYY') = '$(vMaxODSRefresh)';

[#3]

Still no results.  I think that causes it to treat the whole variable command as a string. 

[MK_QSL]

Let vMaxODSRefresh  = Num(Date#(Peek('Enrol_Download_Datestamp', 0, '[MAX ODS Refresh Date]'),'DD/MM/YYYY'))

Try above

[MK_QSL]

and

DATE#'(Enrol_Download_Datestamp,'DD/MM/YYYY') = '$(vMaxODSRefresh)';

[#6]

Sorry, still nothing, and within the variable overview it now shows vMaxODSRefresh as blank

[#7 · Accepted solution]

Actually, I took off the Num() part of your suggestion and it appears to have worked. 

Must have been that date() didn't work but date#() does.  Thanks

[sunny_talwar]

What about this:

[MAX ODS Refresh Date]:

LOAD Max(Enrol_Download_Datestamp) as Enrol_Download_Datestamp

from [Data\Student Numbers\ODS_Snapshots2.QVD] (qvd);

LET vMaxODSRefresh = Peek('Enrol_Download_Datestamp', 0, 'MAX ODS Refresh Date');

DROP Table [MAX ODS Refresh Date];

[Student Numbers]:

LOAD SCJ_KEY,

     DATE(Enrol_Download_Datestamp,'DD/MM/YYYY'),

     Enrol_Report_CRS_Title,

     Enrol_Report_Faculty,

     Enrol_Report_Department,

     Enrol_Report_OnOff_Campus,

     Enrol_Report_HOandEU_or_OS,

     Enrol_Report_HO_or_EU_or_OS,

     Campus_ID,

     Campus_Name,

     FAC_UDF4,

     DupMarker,

     Enrol_Report_Faculty & Enrol_Report_Department & SCE_CRSC as STU_KEY

FROM

[Data\Student Numbers\ODS_Snapshots2.QVD] (qvd)

Where Enrol_Download_Datestamp = $(vMaxODSRefresh);

[sunny_talwar]

I think don't use the Date function in the let variable and also remove the square brackets from your table name within the Peek function:

LET vMaxODSRefresh = DATE(peek('Enrol_Download_Datestamp', 0, '[MAX ODS Refresh Date]'),'DD/MM/YYYY');