Add calculated field from previous field in loading script

[marcel_olmo]

Hi guys,

I have a table like this :

Transaction	Credit	Debit
1	1000	0
2	0	10
3	0	10
4	0	10
5	5	10
6	0	10

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

Transaction	Credit	Debit	NewDesiredColum
1	1000	0	1000
2	0	10	990
3	0	10	980
4	0	10	970
5	5	10	965
6	0	10	955

How could I get it?

Regards, Marcel.

[vamsee]

Try

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

[vamsee]

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]

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 · Accepted solution]

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]

Thanks a lot Vamsee!

Regards, Marcel.

[vamsee]

Glad, it worked for you.