topic Re: Add calculated field from previous field in loading script in QlikView

Add calculated field from previous field in loading script

marcel_olmo — Tue, 11 Sep 2018 18:21:57 GMT

Hi guys,

I have a table like this :

And I'd like to create another column to get a "closed account balance" field. It has to be like this :

How could I get it?

Regards, Marcel.

vamsee — Tue, 11 Sep 2018 18:32:05 GMT

Try

LOAD *,
ALT(Credit,0) + ALT(Peek(New_Desired_Column),0) - ALT(Debit,0) as New_Desired_Column;

vamsee — Tue, 11 Sep 2018 18:33:20 GMT

ALT() is just to make sure it has a numeric value. If the source data does not have nulls then ALT() can be avoided.

marcel_olmo — Tue, 11 Sep 2018 18:44:32 GMT

Thanks vamsee for the try.

I'm trying to do it, but it hasn't worked.

Here's attached the example following your instructions.

vamsee — Tue, 11 Sep 2018 18:48:15 GMT

Your Credit and Debit fields were not being treated as Numbers.

Added the following code and it worked.

LOAD Transaction,
NUM#(Credit, '######') as Credit,
NUM#(Debit,'#######') as Debit ;

PFA.

marcel_olmo — Tue, 11 Sep 2018 18:51:44 GMT

Thanks a lot Vamsee!

Regards, Marcel.

vamsee — Tue, 11 Sep 2018 18:54:00 GMT

Glad, it worked for you.