load qvd

[yelin_nyu]

data are being store into weekly bucket.

Two sets of data Order info and Qty info (ie. Order20130101 & Qty20130101)

However, data in Order is current, data in Qty is as of last week (qty info comes in a week later than order info)

I used 2 approached to load the data into my application

method 1:

load * from 'Order*.qvd' (qvd);

left join load * from 'Qty*.qvd' (qvd);

table came out wrong

However if i do this:

tbl1:

load * from 'Order*.qvd' (qvd);

store tbl1 into QVD1.qvd (qvd);

tbl2:

load * from 'Qty*.qvd' (qvd);

store tbl 2 into QVD2.qvd (qvd);

In my application, I write

load * from QVD1.qvd (qvd);

left join load * from QVD2.qvd (qvd);

The resulting table come out correct. Why?

why there is a difference?

[danielrozental · Accepted solution]

When you use a wild card in load only the first left join will be done against the left table, the 2nd will be done against left already joined to first right table.

If you have QTY1.QVD, QTY2.QVD, QTY3.QVD doing a left join to QTY*.QVD will be like doing

ORDERS:

LOAD

FROM ORDER*.QVD

LEFT JOIN (ORDERS)

LOAD

FROM QTY1.QVD

LEFT JOIN (ORDERS) //It will try to join all fields, not just the ones in Orders and Qty

LOAD

FROM QTY2.QVD

LEFT JOIN (ORDERS)

LOAD

FROM QTY3.QVD

Hope its clear now why it doesn't work.

[jonathjl]

what's the right way to do this then? My method 2 seems very inefficient, although it works.

[#4]

U can write script like below

Order:

Load * from Order*.qvd;

QTY:

Load * form QTY*.qvd;

Left join(Order)

load * resistant QTY;

Kabilan K