New calculated field in Load Script (2 tables 2 conditions)

[#1]

Hello Qlikview Community

I am loading Posted invoices and Electronic XML invoices from Oracle.

In the process I realized that Credit Memos were not handled as expected as they sometimes is negative and sometimes positive values in amount in the XML version of the Invoice.

Basically I need to test if INVOICE_TYPE is CREDIT and AMOUNT => 0 then * -1 otherwise * 1 in the load script and create a new field with the calculated value (AMOUNT_CORR) in the XML_INVOICE Table.

INVOICE:

INVOICE_ID	INVOICE_TYPE
1000	CREDIT
2000	INVOICE
3000	CREDIT

XML_INVOICE

INVOICE_ID	LINE	AMOUNT	ITEM_ID
1000	1	10	11
2000	1	10	15
2000	2	15	17
3000	1	-20	15
3000	2	-10	11

I tried something like the below script, but cant seem to work it out properly. I saw a similar request on the community but the user only had 1 condition (INVOICE_TYPE) and I have to test 2 conditions before calculating.

INVOICE:

Load

     INVOICE_ID

     INVOICE_TYPE

FROM ...;

TYPEMAP:

MAPPING LOAD DISTINCT

     INVOICE_ID as A

     INVOICE_TYPE as B,

Resident INVOICE;

XML_INVOICE

LOAD

     INVOICE_ID

     if(applymap('TYPEMAP', INVOICE_ID)='CREDIT' AND AMOUNT => 0, -1, 1) * AMOUNT as AMOUNT_CORR

FROM ...;

Any suggestions would be greatly appreciated

Thanks

Anders

[#2]

Hi Anders, You don't need a Resident table for Mapping table creation. Please find the below script:

TYPEMAP:

MAPPING

LOAD DISTINCT //You can use LOAD or SELECT directly depend on the data source

     INVOICE_ID ,

     INVOICE_TYPE

FROM ....;

// Please use the IF cond in the precedent load to avoid the confusion.

XML_INVOICE:

LOAD INVOICE_ID ,

          LINE ,

          AMOUNT,

          ITEM_ID ,

          IF(INVOICE_TYPE = 'CREDIT' AND AMOUNT >= 0 , -1 , 1) * AMOUNT AS AMOUNT_CORR

;

LOAD INVOICE_ID ,

          APPLYMAP('TYPEMAP',INVOICE_ID , 'xxx') AS INVOICE_TYPE ,

          LINE

          AMOUNT,

          ITEM_ID

FROM .... ;

[#3]

Hi Dathu

I will test out your suggestion.

(Datasource is QVD files) I should remember to say that another time.

I already accepted Massimos answer as it worked out the problem.

Kind regards

Anders