Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 .... ;
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