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

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

Sun, 17 Mar 2013 08:07:41 GMT

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

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

er_mohit — Sun, 17 Mar 2013 08:29:43 GMT

NUM (debitAmount) - NUM(creditAmount) as Amount

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

Sun, 17 Mar 2013 08:49:26 GMT

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?

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

er_mohit — Sun, 17 Mar 2013 09:04:55 GMT

    NUM(debitAmount) as debitAmount,

    NUM(creditAmount) as creditAmount,

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

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

Sun, 17 Mar 2013 10:00:34 GMT

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).

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

swuehl — Sun, 17 Mar 2013 10:22:02 GMT

Have you already tried evaluate(debitAmount + creditAmount) ?

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

Sun, 17 Mar 2013 10:26:05 GMT

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?

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

swuehl — Sun, 17 Mar 2013 10:29:17 GMT

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.

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

jrodriguez_sand — Thu, 12 Sep 2019 14:22:52 GMT

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,',','.')))

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

maxronny — Thu, 12 Sep 2019 15:58:29 GMT

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