topic Re: Get Maximum And Minimum Year From Resident Table in QlikView

Get Maximum And Minimum Year From Resident Table

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

I have a QVD file of loaded date with a date and year field.

I want to limit my generated calendar for the date to the range of years between the minimum and maximum value in the year field.

I have coded:-

MinMaxDate:

Load min(InvoiceYear) As vMinYear

max(InvoiceYear) As vMaxYear

resident MyLoadedQVD;

TRACE >>1>> $(vMinDate);

TRACE >>2>> $(vMaxYear);

The trace shows no value for vMinDate or vMaxDate.

I don't understand why.

The InvoiceYear field has values and no nulls.

Do I have to declare the variables first in some way for them to receive the min() and max() values?

Any advice appreciated.

Re: Get Maximum And Minimum Year From Resident Table

sunny_talwar — Tue, 08 Mar 2016 15:34:23 GMT

Try this:

MinMaxDate:

Load min(InvoiceYear) As MinYear

max(InvoiceYear) As MaxYear

resident MyLoadedQVD;

LET vMinYear = Peek('MinYear');

LET vMaxYear = Peek('MaxYear');

TRACE >>1>> $(vMinDate);

TRACE >>2>> $(vMaxYear);

Re: Get Maximum And Minimum Year From Resident Table

Digvijay_Singh — Tue, 08 Mar 2016 15:42:54 GMT

Small correction -

TRACE >>1>> $(vMinYear);

Re: Get Maximum And Minimum Year From Resident Table

sunny_talwar — Tue, 08 Mar 2016 15:44:18 GMT

good eye

Re: Get Maximum And Minimum Year From Resident Table

rebelfox — Tue, 08 Mar 2016 16:06:30 GMT

Yes that sorted it. Thanks.

Re: Get Maximum And Minimum Year From Resident Table

swuehl — Tue, 08 Mar 2016 17:16:00 GMT

Roy, it seems there was initially a little confusion about how to use variables vs. table fields.

Has this been sorted as well?

If you want to get min / max values to create a calendar, you can even get rid of the variables completely (following taken from Rob's Better Calendar Scripts | Qlikview Cookbook , always a good read, and modified slightly for year extrema), have a look at lines 19ff:

MasterCalendar:
Load
TempDate AS InvoiceDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(Makedate(minyear) + IterNo()-1) AS TempDate
WHILE Makedate(minyear) + IterNo()-1 < Makedate(maxyear+1);

//=== Get min/max year from Field ===/
LOAD
min(FieldValue('InvoiceYear', recno())) as minyear,
max(FieldValue('InvoiceYear', recno())) as maxyear
AUTOGENERATE FieldValueCount('InvoiceYear');

Re: Get Maximum And Minimum Year From Resident Table

Colin-Albert — Tue, 08 Mar 2016 17:23:01 GMT

Do you really need to generate the max and minimum date?

Often the start date is known and can be hard-coded, and the end date can be calculated based on todays date.
It may be today(), yearend(today()) or possibly today + 12 or 24 months.

Using a simple expression like this can be significantly faster than calculating the min and max date every time you build the calendar, especially when dealing with large data sets.