Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
NUM (debitAmount) - NUM(creditAmount) as Amount
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
What am I doing wrong?
NUM(debitAmount) as debitAmount,
NUM(creditAmount) as creditAmount,
NUM(debitAmount) - NUM(creditAmount) as Amount,
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).
Have you already tried evaluate(debitAmount + creditAmount) ?
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?
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.
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,',','.')))
(Evaluate(debitAmount) - Evaluate(creditAmount)) as Amount,