Script problem

[#1]

I have two tables, Sales Header table and a Sales Item Detail table. Both tables have invoice number with is the link between the two tables. The header file as a field noting the type of invoice (R - regular, D - debit meno, C - credit memo). The item detail table has the sales amounts, but if the header shows a C them I need to convert the amount in the detail table to a minus number. How do write the script to do this in the item detail table?

Thanks,

Stephen

[Anonymous]

Stephen,
You can use mapping:

Header:
LOAD
 InvoiceNumber,
 InvoiceType,
 ...
;
TypeMap:
MAPPING LOAD DISTINCT
 InvoiceNumber, as A,
 InvoiceType as B
RESIDENT Header:
//
ItemDetail:
LOAD
 InvoiceNumber,
 if(applymap('TypeMap', InvoiceNumber)='C', -1, 1) * SalesAmount as SalesAmount,
...
;

[johnw]

Mapping is probably the best and fastest approach, and is the approach I would probably use here. But to give you options, you could also:

Header:
LOAD
InvoiceNumber,
InvoiceType,
...
;
ItemDetail:
LOAD
InvoiceNumber,
SalesAmount as TempSalesAmount,
...
;
LEFT JOIN LOAD
InvoiceNumber,
InvoiceType
RESIDENT Header
;
LEFT JOIN LOAD
InvoiceNumber,
if(InvoiceType='C',-1,1)*TempSalesAmount as SalesAmount
RESISENT ItemDetail
;
DROP FIELD TempSalesAmount
;

[#4]

Thank you Michael and John. I did use mapping and it is working perfectly.

Stephen