Cross referencing a field between 2 qvds based on a criteria
We have credits and invoices as part of our load script from the QVDs.
What I would like to do for the LINECOST in credits, is to say If linecost = 0 or null then take the LINECOST from the invoice, is this easily possible? What I was thinking of doing is WHERE LINECOST <> 0 but then it would just be filtering out any with linecost doesn't equal zero which isn't exactly what i'd like. I have been looking at crosstable but i'm not sure if this is really along the same lines.
LOADTransactionID, ITEM, LINECOST, NetSalePrice, QuantityInvoiced FROM CreditItems.QVD (qvd);
LOADInvoices, TransactionType, TransactionID, InvoiceID, Customer, TransactionDate, InvoiceDate, InvoiceNumber, SALESM_CODE, PONUM FROM Hist*Transactions.QVD//Load all history //Hist201*Transactions.QVD //Load only history starting with 201 - ie 2010 onwards (qvd);