Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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