A Historical Odyssey: QlikView 7, QVD files and the Aggr() function

[hic]

For QlikView 7 we developed a number of features that would enable developers to make even more advanced applications. One such feature was the Buffer prefix – a prefix that you could put in front of a Load or a SELECT that would store the data on the local machine and automatically use the local data when appropriate. With it, you could e.g. load data from a slow ODBC connection just once a day and for other script runs use the buffer.

The buffered data needed to be compact and something that QlikView could load fast. So, the QVD file was invented to solve this need. Today, hardly anyone uses the Buffer prefix, but QVD files created with the Store command are often used.

Another new feature was the Aggr() function. Already for QlikView 4 we had a solution for having an aggregation as dimension. Well, solution is perhaps not the right word… There was a kludgy, hidden feature with which you could use a count of a field as dimension. Internally it was called the Doctors’ Special. We solved a customer’s problem at the time, but we were not satisfied: we instead wanted a general, good solution for nested aggregations.

Six years later – after much thinking – Håkan (the Inventor) came up with the Aggr() function. It is a general function that can be used both as dimension and as measure. It can be nested and it can internally use any aggregation. In other words: it is a general function for nested aggregations. A by-product was the calculated dimension, which was necessary in order to use the Aggr() as dimension.

The Intel Itanium processor was first released in 2001, but its sales had still not picked up when QlikView 7 was released four years later in 2005. Instead, a new kid appeared on the block: The AMD X64 architecture. The X64 turned out to be both cheaper and faster than the Itanium, and as a consequence it sold better.

We already had an Itanium edition of QlikView and we realized that we also needed an X64 edition. Porting the code was straightforward and QlikView 7 was now shipped in three editions: X86, IA64 and X64.

QlikView 7 also brought the calendar object, the block chart, the box plot, the expression overview, the variable overview, the alerts and the reports.

As a curiosity, I can also mention that QlikView 7 was the first version with scroll bars for the sheet. None of the previous versions had had this. We had always had the opinion that scroll bars would steal screen space and not add any relevant functionality. Instead we had a zoom function so that you could fit the work area to the screen. But - it’s never too late to change one’s mind…

HIC

Further reading on the Qlik history:

A Historical Odyssey: QlikView 6 and Multi-threading

A Historical Odyssey: QlikView 8, Ajax and Set Analysis

A Historical Odyssey: The Doctors’ Special

[Gysbert_Wassenaar]

One thing I've been wondering about is why the aggr() function is distinct by default. Load is nodistinct by default, count/sum/max/min etc are nodistinct by default. But aggr is the odd one out afaict. That still occasionally trips me up.

But - it’s never too late to change one’s mind…

So... any chance of changing the aggr function to bring it in line with the rest?

[hic]

When we introduced the Aggr(), we dicussed this and came to the conclusion that the distinct case was the most common one. So we chose distinct as default.

As I see it, the Aggr() function will - as function seen - always be the odd one out, no matter what. It is a one-of-a-kind function. It is not an aggregation function. Instead, it returns an n-dimensional hypercube similar to a straight table; an array in the 1-dim case. It should be compared to a SELECT that contains a GROUP BY, and such a SELECT is in SQL always distinct. So I think it is logical that the Aggr() by default is distinct.

Finally, changing it would cause problems with backward compatibility. So, no, we will not change this one.

HIC    (... I love questions like that! )

[mr_barriesmith]

When will we see the RAM space used by the aggr result in Sheet properties Objects tab?

With a pseudo chart name aggr01!

AP Barrie-Smith

0828511026

Expert Services Manager

QlikView South Africa

[Gysbert_Wassenaar]

It should be compared to a SELECT that contains a GROUP BY, and such a SELECT is in SQL always distinct.

So, nodistinct can be the default and the group by will make it distinct and adding a distinct keyword to the select statement wouldn't change the result. It would just be superfluous.

Isn't that the same in almost all of the cases aggr is used? If nodistinct was the default then it would almost never be necessary to specify the distinct keyword to make the result distinct? As it is now sometimes nodistinct must be added or wrong results appear. But if nodistinct were the default then wouldn't the result almost always be distinct anyway? And the cases where now nodistinct must be added would then give the correct result too?

I guess I must be missing something, because some rather clever Swedes obviously put quite a bit of thinking in this. Can you point out what I'm missing?

[hic]

No, if nodistinct was default, you would in most cases get incorrect results. An example:

With the following source data

you would get the following internal table(s)

and the following outer aggregations

So, nodistinct will in many cases return the wrong number.

HIC

[Gysbert_Wassenaar]

Ah, of course. Silly me. Thanks for the explanation.

[#8]

Still the best QlikView Version ever for me. The features introduced were massive and the product was so stable that it brought to us quite a challenge to make customers migrate later on to 8...

Always nice to read these posts!

Best,

Rodrigo

[pat_agen]

We got the offset attribute in bar charts enabling waterfall charts - the "killer" app for our users. It didn't work first up but support in Lund fixed it in double quick time and we never looked back.

Moved to a 64 bit server and got to use more than 3G of RAM for the qvs process. I don't know how we managed before.

[IAMDV]

Thank you very much HIC. Very useful explanation about DISTINCT qualifier for AGGR(). I always think AGGR() as GROUP BY clause in SQL. Your explanation was clear and very useful.

However, I would expect Concat() function to behave as Aggr() function. I mean, I would like to think that Concat() function should return distinct values by default unless we use NODISTINCT qualifier. Again, I might be wrong but that's just my understanding. And more interesting one is the ONLY() function which allows DISTINCT qualifier... why do we need DISTINCT qualifier for ONLY()?

[hic]

I agree that I rarely use Concat() in a nodistinct mode. The distinct values are what you want - normally. But I think that it is good that it works like all the other aggregation functions: The default behaviour is to use all records, no matter if they are distinct or not.

I also agree that the distinct qualifier in the Only() function is an oddity. It is distinct in its nature! But also here I think it is good that the same principles are used for all aggregation functions.

Concerning Aggr(): Here the distinct is not applied on the values of the individual records (as it is for all aggregation functions) but instead on the values of the grouping symbol. So, again, the Aggr() function is the odd one out...

HIC