Problem: Calculation in load script using EVALUATE doesn't work

[#1]

Hi all,

I try to load data from an auditfile. In the auditfile the debitAmount and the creditAmountfield is text.

I used the EVALUATE function in my loadscript to convert them to numbers. That's okay.

But after that, I tried to make a calculation in the LOAD script: EVALUATE (debitAmount) - EVALUATE (creditAmount) as Amount

That doesn't work. It seems that EVALUATE is not useful to calculate (a calculation EVALUATE (debitAmout) + 100 works fine but a double EVALUATE in the calculation doesn't work).

How can I realize the calculated field Amount as the difference between debitAmount and the creditAmount?

I hope you have the solution.

Regards,

Rudy

[er_mohit]

NUM (debitAmount) - NUM(creditAmount) as Amount

[#3]

Thank you for your reply, but it doensn't work.

My script is:

LOAD

recordID,

    accountID as grtbkreknr,

    custSupID,

    documentID,

    effectiveDate as link_Date,

    description,

    Evaluate(debitAmount) as debitAmount,

    Evaluate(creditAmount) as creditAmount,

    NUM(debitAmount) - NUM(creditAmount) as Amount,

    [vat/vatCode] as vatCode,

    [vat/vatAmount] as vatAmount

   // Key to parent table: auditfile/transactions/journal/transaction

FROM (XmlSimple, Table is [auditfile/transactions/journal/transaction/line]);

What am I doing wrong?

[er_mohit]

    NUM(debitAmount) as debitAmount,

    NUM(creditAmount) as creditAmount,

    NUM(debitAmount) - NUM(creditAmount) as Amount,

[#5]

I does not work: using NUM there is no output (=0) but when I use EVALUATE te output is correct.

When I use: EVALUATE+100 then it gives a correct output, but EVALUATE (debitAmount)+EVALUATE(creditAmount) gives an error (=0).

[swuehl]

Have you already tried evaluate(debitAmount + creditAmount)  ?

[#7]

I tried this but it doesn't work also because they are both textformats so it seems they must be handled seperately.

Frustrating isn't it?

[swuehl]

Would it be possible to upload your input file? So we can have a closer look to the format you need to read in.

The standard procedure would be using QV function num#(TEXT, FORMATCODE) to interprete a text as a number.

[jrodriguez_sand]

Hola Buenas, yo estuve peleándome con el evaluate y me tocó sustituir el signo coma ',' por el punto '.' con un replace en los campos numéricos para que funcionase. algo así...

Evaluate(Num#(Replace(Valor,',','.')) + Num#(Replace(Valor2,',','.')))

[maxronny]

(Evaluate(debitAmount) - Evaluate(creditAmount)) as Amount,