topic Script problem in QlikView

Script problem

Tue, 09 Jun 2009 01:44:00 GMT

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

Script problem

Anonymous — Tue, 09 Jun 2009 01:53:54 GMT

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,
...
;

Script problem

johnw — Tue, 09 Jun 2009 03:01:47 GMT

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
;

Script problem

Tue, 09 Jun 2009 04:39:29 GMT

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

Stephen