Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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.
Credits:
Concatenate (Transactions)
LOAD TransactionID,
Customer,
TransactionType,
"INV_NO",
InvoiceNumber,
"SALESM_CODE",
TransactionDate,
CreditDate,
WAREHOUSE
FROM
Credits.QVD
(qvd);
Concatenate (TransactionDetails)
LOAD TransactionID,
ITEM,
LINECOST,
NetSalePrice,
QuantityInvoiced
FROM
CreditItems.QVD
(qvd);
Invoices:
Transactions:
LOAD Invoices,
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);
Concatenate
LOAD Invoices,
TransactionType,
TransactionID,
InvoiceID,
Customer,
TransactionDate,
InvoiceDate,
InvoiceNumber,
SALESM_CODE,
WAREHOUSE,
CONSULTANT_NAME_C,
PONUM
FROM
Transactions.QVD
(qvd);
TransactionDetails: // PT added v 0.2 - all fields loaded already
LOAD TransactionID,
ITEM,
QuantityInvoiced,
NetSalePrice,
LINECOST,
CRT,
Z_ASSOC_ROW
FROM
Hist*TransactionDetails.QVD
//Hist201*TransactionDetails.QVD
(qvd);
Concatenate
LOAD TransactionID,
ITEM,
QuantityInvoiced,
NetSalePrice,
LINECOST,
CRT,
Z_ASSOC_ROW
FROM
TransactionDetails.QVD
(qvd);
- load the invoices
- create a mapping table from the invoices table
Map:
mapping load
FIELD, LINECOST
resident TransactionsDetails;
replace FIELD with the join field (or fields using field1 & '-' & field2) between invoices and credits
- load credits using the mapping table
LOAD TransactionID,
ITEM,
if(len(trim(LINECOST))=0, ApplyMap('Map', FIELD, LINECOST) as LINECOST
NetSalePrice,
QuantityInvoiced
FROM CreditItems.QVD (qvd);
Hi Max,
Many thanks for the reply, I botched another field that I can use to combine them as i'm not sure how they are concatenated against for the minute.
I do get some errors when I do it,
Table not found
Map:
mapping load
INV_NO, LINECOST resident TransactionalDetails
And
Error in expression:
')' expected
Concatenate (TransactionDetails)
LOAD TransactionID,
ITEM,
if(len(trim(LINECOST))=0, ApplyMap('Map', INV_NO, LINECOST) as LINECOST
NetSalePrice,
QuantityInvoiced
FROM
CreditItems.QVD
(qvd)
I also think it is technically null as it is showing as - in the table view, maybe I told you the wrong information
I think you miss a bracket, a comma and the else part of the if (LINECOSt > 0)
LOAD TransactionID,
ITEM,
if(len(trim(LINECOST))=0, ApplyMap('Map', INV_NO, LINECOST) as LINECOST
NetSalePrice,
QuantityInvoiced
FROM
CreditItems.QVD
(qvd)
try with
LOAD TransactionID,
ITEM,
if (len(trim(LINECOST))=0,
ApplyMap('Map', INV_NO, LINECOST), /* LINECOST=0, try to get from mapping table using INV_NO */
LINECOST /* LINECOST <>0, use LINECOST */
) as LINECOST,
NetSalePrice,
QuantityInvoiced
FROM CreditItems.QVD (qvd);
there is a INV_NO field in CreditItems.qvd ?
Thanks Max, There isn't any INV_NO as part of CreditItems table in the qvd, CreditItems is linked to Credits via TransactionID which has the INV_NO.
The same with invoices, but TransactionDetails to Transaction though there is a Syn_2 going on with that.