Load only year end dates

[LoKi_asterix]

Hi all,

How can I load only the last VALUE_DATE per year (i.e., year-end transactional dates) from a QVD in my script?

Where VALUE_DATE = YearEnd(VALUE_DATE)  is not cutting it for it.

Thanks!

[Or]

YearEnd() adds a time component of 23:59:59. If you're just looking for a date, you'll need to either Floor() or DayName() the YearEnd() result.

Note that for performance reasons, it might be better to do something like:

Dates:

Load MakeDate(Year(Today()-RowNo(),12,31) as VALUE_DATE

Autogenerate(10); // To get ten years back, if you want more or less change this number

Load * From YourQVD Where Exists(VALUE_DATE);

DROP TABLE Dates;

[LoKi_asterix]

Thanks for the input. However, the year-end date isn't always 31/12/**** in this dataset. I'd like to filter the data to return only the maximum VALUE_DATE per year (i.e., the latest Value date each year).

[Or · Accepted solution]

Then you would need to find out what that date is, typically by using something like

Select Year(VALUE_DATE) as Year, Max(VALUE_DATE) as VALUE_DATE

From YourTable

GROUP BY Year(VALUE_DATE);