topic Re: Loading From a QVD Where Date is Equal to a Variable - Help! in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

MK_QSL — Wed, 08 Jun 2016 12:09:07 GMT

use

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

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

Wed, 08 Jun 2016 12:15:20 GMT

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

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

MK_QSL — Wed, 08 Jun 2016 12:39:11 GMT

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

Try above

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

MK_QSL — Wed, 08 Jun 2016 12:39:53 GMT

and

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

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

Wed, 08 Jun 2016 12:45:28 GMT

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

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

Wed, 08 Jun 2016 12:49:07 GMT

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

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

sunny_talwar — Wed, 08 Jun 2016 12:59:00 GMT

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

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

sunny_talwar — Wed, 08 Jun 2016 13:01:00 GMT

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