topic Re: Load only year end dates in App Development

Load only year end dates

LoKi_asterix — Sun, 25 May 2025 07:51:50 GMT

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!

Re: Load only year end dates

Or — Sun, 25 May 2025 10:01:42 GMT

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;

Re: Load only year end dates

LoKi_asterix — Sun, 25 May 2025 10:07:09 GMT

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

Re: Load only year end dates

Or — Sun, 25 May 2025 10:51:34 GMT

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