topic Re: Left Join Load .... from INVOICES*.txt, is it possible? in QlikView

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

derekjones — Wed, 19 Jun 2013 16:51:00 GMT

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

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

Gysbert_Wassenaar — Wed, 19 Jun 2013 16:57:51 GMT

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);

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

derekjones — Wed, 19 Jun 2013 17:00:13 GMT

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