Cross referencing a field between 2 qvds based on a criteria

[james_hanify]

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

[maxgro]

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

[james_hanify]

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

[maxgro]

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 ?

[james_hanify]

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.