When you do this:
SELECT Field1, Field2
FROM MyTable
WHERE whatever;
QlikView is letting you get away with a shortcut syntax. A more full syntax might look like this:
LOAD *;
SQL
SELECT Field1, Field2
FROM MyTable
WHERE whatever;
Basically, there's an implied load of all fields whenever you do a select in QlikView. This form of the load is called a "preceeding load". What it does is load its data from the results of the very next statement, in this case the SQL SELECT.
Now, that has very little to do with the example and how it works, but I wanted to point out that in a sense, you're already using LOAD statements; you just haven't been writing them explicitly.
Let me start by just explaining how my example works, and then you can see if you can figure out how to apply it to your own data. If you're still having problems, then we can look at your specific data next.
This part...
Data:
LOAD * INLINE [
Type, Year, Value
...
];
Is called an "inline load". When we write examples, we typically use inline loads instead of selects because we don't have access to your data and you don't have access to our data. An inline load can be reloaded by anyone, so makes for good examples. The idea is that the inline load is standing in for your SELECT statment. In your actual application, you probably have something that looks more like this:
Data:
SELECT Type, Year, Value
FROM SomeTable
WHERE whatever;
These two bits of script are equivalent, at least in the sense that they'll each load up a table called "Data" with fields Type, Year and Value. One just grabs the data from the text in the script, while the other reads some database using SQL.
There are a LOT of things that you can load from, though, and an inline table and an SQL SELECT are just two of them. The very next LOAD demonstrates another possible source:
Types:
LOAD fieldvalue('Type',iterno()) as Type
AUTOGENERATE 1
WHILE len(fieldvalue('Type',iterno()))
;
The source here is AUTOGENERATE. It says to autogenerate only 1 row, but there's a WHILE loop here as well. So it will create rows as long as the while condition is true. The while condition is a bit complicted. First, let's talk about the fieldvalue() function. Internally, QlikView keeps a table of the distinct values of every field, I'm fairly certain in the order in which they were loaded. So even though you can't directly reference it as a table, somewhere internal to QlikView there's a data structure that looks something like this:
Type:
Row, Value
1, Mainframe
2, DotNet
3, Java
The fieldvalue() function takes advantage of this structure. So if I ask for fieldvalue('Type',2), it should return 'DotNet'. But instead of asking for a specific row, I'm asking for the row at iterno(). Iterno() is a fuction that returns where we are in the while loop. So the while loop will generate an iterno() = 1 on the first pass, then 2, then 3, and so on. So we are, then, looping through this internal table of the values of the Type field. Now, the while loop ends when the condition is no longer true. The condition is len(...). I want the length of the value returned by the fieldvalue() function. Internally, zero is false, and any non-zero value is true. So as long as I'm getting values back, this will be true. Once there are no more values, the len() will return 0, which is interpreted as false, and the loop ends.
So this load is essentially taking QlikView's internal table of the values of Type and making it accessible by turning it into a real table. We'll end up with this actual table:
Types:
Type
Mainframe
DotNet
Java
Now, why did we bother doing this? Couldn't we have gotten the same results with this simpler and clearer load?
Types:
LOAD DISTINCT Type
RESIDENT Data;
Yes, that would give us the same results. But it has to load from a table that has, in the example, six rows instead of from one that only has three rows. That doesn't matter much. But out in the real world, data tables might have tens or hundreds of millions of rows, but only a handful of values on those rows that we care about. It's then MUCH more efficient to load in those values from only a handful of rows than from tens or hundreds of millions of rows. So that's why I went to the additional effort, even though it doesn't matter for the example.
The next load demonstrates yet another source for data, a RESIDENT table:
Totals:
LOAD
'Total' as TypeGroup
,Type
RESIDENT Types
;
We use the RESIDENT keyword to tell it we're loading from a table that we've already loaded, that it already has in memory. In this case, we've already built the Types table. Now we want to take advantage of it to build yet another table. The result of this load will be this, and hopefully it's clear why:
Totals:
TypeGroup, Type
Total, Mainframe
Total, DotNet
Total, Java
Then we do another resident load, but this time, we tell it to CONCATENATE the data onto the Totals table. Concatenating means to just add everything new as new rows without trying to match up any values. It's like doing a UNION ALL in SQL. So the result after doing the concatenate will be this:
Totals:
TypeGroup, Type
Total, Mainframe
Total, DotNet
Total, Java
Mainframe, Mainframe
DotNet, DotNet
Java, Java
And that's the table I suggested generating. So the script generates the table I suggested.
Now, how does this table solve our problem? Well, we have four TypeGroups, Total, Mainframe, DotNet and Java. The Total TypeGroup connects directly to ALL of the Type values. So when doing a sum for the Total TypeGroup, QlikView will be summing the data for ALL of the Types. All of the other TypeGroups only map to a single Type. Therefore, all of the other TypeGroups will behave as if we were just looking at that Type, and will sum the data only for that type.
