Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
where Snapshot_Week = $(maxdate);
Or is there a slicker way to do this?
Any help appreciated. Thanks.
Your syntax to set the variable is wrong. It should be:
maxtable:
Load date(max(Snapshot_Week)) as maxweek
from
;
LET maxdate = peek('maxweek');
-Rob
Your syntax to set the variable is wrong. It should be:
maxtable:
Load date(max(Snapshot_Week)) as maxweek
from
;
LET maxdate = peek('maxweek');
-Rob
MaxWeek:
LOAD max(Snapshot_Week) as MaxWeek
FROM
LET vMaxWeek = PEEK('MaxWeek');
DROP TABLE MaxWeek;
Data:
LOAD Vendor_ID as [Vendor ID],
Snapshot_Date,
Snapshot_Week
FROM
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).
Your variable declaration is wrong for max week
MaxTable:
Load
max(Snapshot_Week) as MaxWeek
from
Let maxWeekdate = Peek('MaxWeek',0,'MaxTable');
FinalTable:
LOAD
Vendor_ID as [Vendor ID],
Snapshot_Date,
Snapshot_Week
FROM
where Snapshot_Week = $(maxWeekdate);
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);