My personal notes

As your data gets bigger, small things add up.

Getting Minimum and Maximum dates are a simple task, but with couple hundred million records, it takes long time to get them.

Commonly, following code is used to get the minimum and maximum dates.

1

MinMaxDates:
Load
Min(Date) as MinimumDate,
Max(Date) as MaximumDate
Resident Transactions;

or

2

MinMaxDates:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
From Transactions.QVD (qvd);

Most of us knows Optimized QVD load is the best way of loading QVD data, but once the data is transformed using QV functions or combining fields, etc, the data has to be unpacked and it no longer performs optimized load.

I find loading from QVD is faster than using resident table when you have a very large data set and also you can utilize Optimized QVD load which is super fast.

Another thing you need to know about the data is that even though you have billions of records, QlikView does not hold duplicated records.  (Please take a look at  Henric Cronström’s Blog post about

Symbol Tables and Bit-Stuffed Pointers). Each field values can be accessed using FieldValue() function.

For example, if you have 100 million transactions for a month of December 2014, Date field contains only 31 unique dates. 12/1/2014 ~ 12/31/2014

Instead of checking Dates from 100 million transactional records, you can use FieldValue() function to retrieve 31 records from Date field.

Here is how.

Let us pretend Transactions.qvd contains 100 million records and Date field has not been populated by Calendar or other tables.

You need to load the data.

Dates:
Load
Date,
//Date as Date_Temp // If you cannot use Date, then rename it.
From Transactions.qvd (qvd); // This is an optimized QVD load and it shouldn't take long.

MinMaxDates:
Load
Min(Date_) as MinDate,
Max(Date_) as MaxDate
;
Load
FieldValue('Date', IterNo()) as Date_
Autogenerate(1)
While not IsNull(FieldValue('Date', IterNo());

Let vMinDate = Peek('MinDate', -1, 'MinMaxDates');
Let vMaxDate = Peek('MaxDate', -1, 'MimMaxDates');

Drop Tables Dates, MinMaxDates;

If your Date is already loaded and it is only available in one table, then you can just use FieldValue() function to get the minimum and maximum dates.

Try to look up following functions in QlikView Help: FieldValue(), FieldIndex(), Peek() and Lookup()

ref:

http://community.qlik.com/thread/72530

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers