Load qvd-files with a join

[#1]

Now do I load my application with a script as below. though these tex files are 
rather big my intention is to create qvd-files of them. The only problem is the 
update for the sales reps which is managed by the join load today.
The structure of the qvd-files is like 'Invoice:'
I wonder if I can do that join load with the qvd-files instead ? Like:

load * from I:\Statistik\QVA\Datafiles\2008\StatKomp2008_gen.qvd (qvd)
join load cunumber1,SalesRep1,SalesRep2 resident Customer_read;

If I can load as above do I have to make the join after every qvd-file ?




This is the script that runs and works today

Invoice_Temp:
LOAD [@1:8] as inv_period1, 

.
.
.
from I:\Statistik\QVA\Datafiles\2013\stat13_All_Dat.txt (ansi, fix, no labels, header is 0, record is line)
 where [@216:223T]<>'GEG';
 join load cunumber1,SalesRep1,SalesRep2 resident Customer_read;

Invoice:
Load inv_period1 as inv_period,
.
.
if(inv_type1='L',SalesRep1,SalesRep2) as inv_salesman,

resident Invoice_Temp
where left(StatHierarki1,2)<>'17' ;


drop table Invoice_Temp;        

[luis_pimentel]

If I am understanding correctly, what you need is a Incremental Load instead of reloading the same data everyday.

Check out this amazing document about how to do it: Incremental Load.docx

Hope that helps.

Luis.

[Gysbert_Wassenaar · Accepted solution]

First load all the qvd's into one table, then do the join.

[stevedark]

Hi Håkan,

When I am given very large CSVs to work with I will generally create QVD's of the CSV files with no manipulation on them at all.  Simply:

CSVFile:

LOAD
     *

FROM MyCSV.csv

(... format fields ...);

STORE CSVFile INTO MyQVD.qvd (qvd);

The QVD's can then be used in exactly the same way as the CSV's were in the load script, just the load will be significantly quicker as the QVD files will be many times smaller than the source CSV.

I see that you have a fixed width file, so the * will need to be replaced with the field definitions.

Having too many JOINs and RESIDENT loads in a script can make it slower.  See if you can do the join from the QVD in one go - rather than loading into a temporary table first.  Also, you may find that a number of ApplyMap statements work more efficiently than the JOIN - you will certainly get more control over the output.

Hope that helps,

Steve