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

to use applyMap you have to be sure that output field will deliver a unique value,

means INVOICE_ID can have only 1 INVOICE_TYPE at a time

is it the case? Because according to your example, invoice_id is related to many line and each line could potentially be invoiced, cancelled, credited, puased or whatever...

Best regards

Chris

[alexandros17]

Try this

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

[#4]

And (i forgot)  are you sure type are always uppercase?

don't hesitate to use upper or lower in mapping table and applymap

Chris

[#5]

Result expected:

A XML_INVOICE TABLE looking like this:

INVOICE _ID	LINE	AMOUNT	ITEM_ID	AMOUNT_CORR
1000	1	10	11	-10
2000	1	10	15	10
2000	2	15	17	15
3000	1	-20	15	-20
3000	2	-10	11	-10

[#6]

Hi Chris

You're absolutely correct. INVOICE_ID will not deliver a unique value.

A new field consisting of INVOICE_ID + INVOICE_LINE_ID could that be an option?

INVOICE_ID is either CREDIT or INVOICE. Lines will not be different types.

Kind regards

Anders

[#7]

Hi Anders

I think you need "AND AMOUNT >= 0" instead of "AND AMOUNT => 0"

Lukasz

[#8]

I get this error

[maxgro · Accepted solution]

if you use Alessandro Saccone epression the result is what you want, see below image and script using Alessandro expression

TYPEMAP:

mapping LOAD * INLINE [

INVOICE_ID, INVOICE_TYPE

1000, CREDIT

2000, INVOICE

3000, CREDIT

];

XML_INVOICE:

load

  *,

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

  ;

LOAD * INLINE [

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

];

[#10]

Hi Massimo

Your answer worked out the problem for me.

At least on my little example, the exciting part is how it will handle 2 million invoices and 4 million lines.

This was my first question on the community and I am amazed.

Thank you very much. You were all very helpful.

Kind regards

Anders