Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Large year ranges

Hi All

I'm trying to handle a data set that has data going back for a 1000 years.

I want to generate a master calendar that covers all dates off from the beginning

of my data set to the end.

When trying to populate the master calendar with dates it only generates a

subset of the years. Is this because I'm overflowing some datatype?

If I use the year function directly on the data in my table I can succesfully generate

a list of years, just not autogenerate a calendar based on min and max.

Any ideas?

Many Thanks

Harry    

Tags (1)
2 Replies
MVP
MVP

Re: Large year ranges

Harry,

I don't think it's a matter of overflowing data types. You could easily check this if you set varMinDate and varMaxDate manually to some appropriate numbers.

I see two issues here:

1)  Your raw Date are values with nine digits and look like 183104242, and you use

date#(mid(Date,1,8),'YYYYMMDD')

to parse your values into Date type. That works just fine with values like 183104242 (April 24, 1831), but you also have values like  177500002 or even just 0. Both will not be succesfully parsed, because they miss an appropriate format (177500002 would imply Month 0, Day 0, which is invalid, same for just 0).

So you need to decide what to do with these values, how to detect and handle them.

2) You use the peek function to retrieve the first and last CalendarDate record from your Dates table. But the table is unsorted, so you won't necessarily get the min and max CalendarDates in your varMinDate and varMaxDate. Because of 1), some rows won't have a valid CalendarDate, and in your sample the last record shows a NULL. This is why your master calendar won't be built correctly.

If you fix 1) and sort your Dates table by Date, the master table should just work fine.

Hope this helps,

Stefan

edit: One more note: You might encounter some issues in displaying CalendarDates with dates b.c. though.

Here it seems that the date formatting  using date() will not display the values correctly.

Not applicable

Large year ranges

Hi Stefan

Many thanks for your reply. Will let you know how I get on. I stupidly forgot

to add the sort to the date field for the peek!

Best

Harry

Community Browser