Multiple Load Keywords in a Single Script

[#1]

What is the difference between

Load X, Y, Z From XYZ.qvd

VS

Load X;

Load Y;

Load Z From XYZ.qvd????

[johnw · Accepted solution]

The first is selecting three fields from a QVD, simple and obvious.

The second is actually doing three loads in a row, and in the opposite order of what you see. FIRST, it loads Z from the QVD. THEN it tries to load Y from what it just loaded. There is no such field in what it already loaded, since you only loaded Z, so it will fail at that point.

To give a more realistic example:

LOAD *
,year(MyDate) as MyYear
;
LOAD
MyID
,MyDate
FROM My.qvd (QVD)
;

First this loads two fields from the QVD, MyID and MyDate. THEN, it goes through every row of that table, and loads those two fields, but adds a new field MyYear. By way of comparison, you can create the exact same table with this simpler script:

LOAD
MyID
,MyDate
,year(MyDate) as MyYear
FROM My.qvd (QVD)
;

But the two scripts will not actually behave the same way. In the first script, the QVD load is an optimized load, which means it will execute very quickly. Adding the new field on top of that will add time, but I suspect the whole thing will still execute more quickly than the second script, which does not use an optimized load. Hard to know without testing, of course.

In any case, the two things to remember are that each load pulls data only from the previous load, and that the previous load in this case is the load BELOW, not the load ABOVE.

[Oleg_Troyansky]

I assume you dont mean this syntax literally... Literally speaking, the second syntax is incorrect...

What you probably mean is how to read a series of preceding loads... When a load statement doesn't have any "from" or "resident" or any other sources, it's called "preceding load" - in this case, it takes the data from the following load statement. Preceding loads are typically used in conjunction with database loads, to rename and process the data coming from the database, and also to re-use new calculated fields, to avoid repeating the same formula numerous times

For example:

load *, AddMonths( BeginningOfMonth , -1) as PreviousMonth;

load *, MonthStart(Date) as BeginningOfMonth;

load ...,

makedate(X,Y,Z) as Date,

... from Data.QVD;

In this example, Date is calculated in the initial (bottom) load. Then MonthStart is calculated in the second preceding load, and the previous month is calculated in the third load. Obviously, this is just to demonstrate the feature...

[#4]

Thanks Oleg and John.

I understand that now.