Set variable to maximum date of a qvd file load

[#1]

I'm sure this is so fundamental as to be embarrassing for me, but I can't find it listed in the two books I have, nor online.  Perhaps I'm not phrasing the question correctly.

I'm trying to filter a qvd load to only bring back records for the latest Snapshot_Week.

Here's what I'm trying, and I can't get it to set the variable:

(This won't work)

Set maxdate =
(Load max(Snapshot_Week)
from (qvd)
group by Snapshot_Week);

(Pretty sure this would work if the variable was populated)

LOAD
  Vendor_ID as [Vendor ID],
     Snapshot_Date,
     Snapshot_Week
FROM
(qvd)
where Snapshot_Week = $(maxdate);

Or is there a slicker way to do this? 

Any help appreciated.  Thanks.

[rwunderlich · Accepted solution]

Your syntax to set the variable is wrong. It should be:

maxtable:

Load date(max(Snapshot_Week)) as maxweek

from (qvd)

;

LET maxdate = peek('maxweek');

-Rob

[Nicole-Smith]

MaxWeek:

LOAD max(Snapshot_Week) as MaxWeek

FROM (qvd);

LET vMaxWeek = PEEK('MaxWeek');

DROP TABLE MaxWeek;

Data:

LOAD Vendor_ID as [Vendor ID],

     Snapshot_Date,

     Snapshot_Week

FROM (qvd)

WHERE Snapshot_Week = $(vMaxWeek);

Note: Have not tried running so there could be syntax errors, but you should get the general idea.  Also, QV must recognize your Snapshot_Week as a date in order for this to work (or it won't know how to take a max of it).

[its_anandrjs]

Your variable declaration is wrong for max week

MaxTable:

Load

max(Snapshot_Week)  as MaxWeek
from (qvd);

Let maxWeekdate = Peek('MaxWeek',0,'MaxTable');

FinalTable:

LOAD

     Vendor_ID as [Vendor ID],
     Snapshot_Date,
     Snapshot_Week
FROM
(qvd)
where Snapshot_Week = $(maxWeekdate);

[#5]

Hi,

TRy this:

T1:

LOAD
  Vendor_ID as [Vendor ID],
     Snapshot_Date,
     Snapshot_Week
FROM
c:\Qlikview\PO.qvd] (qvd)
Order by Snapshot_Date ASC;

LET maxdate = Peek('Snapshot_Date',-1,T1);

drop table T1;

PO:

LOAD
  Vendor_ID as [Vendor ID],
     Snapshot_Date,
     Snapshot_Week
FROM
c:\Qlikview\PO.qvd] (qvd)
where Snapshot_Week = $(maxdate);