Left Join Load .... from INVOICES*.txt, is it possible?

[derekjones]

Hi All

I'm trying to left join invoice details to my sales table and basically both Sales data and Invoice data are originally stored as monthly files e,g.

SALESFY201301.txt

SALESFY201302.txt

INVOICESFY201301.txt

INVOICESFY201302.txt

So I use ....*.txt to load all files starting with same name togeather. My script is basically as follows:

Sales:

LOAD

order_line_id,

order_number,

order_date

FROM SALESFY*.txt;

LEFT JOIN LOAD

order_line_id,

invoice_number,

invoice_date

FROM INVOICESFY*.txt;

However my issue is on the left join the only file which is actually joined is the first one it comes across i.e. INVOICESFY201301.txt and not any after that. Is this a limitation I can't get around and therefore have to load all invoice files in as a seperate table first then left join resident load that to the Sales table? Seems inefficient having to load it all first to then load again to left join.

My thanks in advance for any help

Derek

[Gysbert_Wassenaar]

Yes, you'll have to load all the files first. But by storing the intermediate tables in a qvd and the loading from the qvds for the join you won't lose much time. Loading qvds is very fast.

Sales:

LOAD

order_line_id,

order_number,

order_date

FROM SALESFY*.txt;

STORE Sales into sales.qvd;

Drop table Sales;

Sales:

LOAD

order_line_id,

invoice_number,

invoice_date

FROM INVOICESFY*.txt;

right join load * from sales.qvd (qvd);

[derekjones]

Thanks Gysbert

I suspected as much, pity you can't left join load directly multiple files, had to check before I changed the script.

Derek