11 Replies Latest reply: Feb 5, 2015 2:59 AM by federico cal

Hi community.

I have this situation

 DATE_FROM DATE_FROM_DATE_TO IN_PORTFOLIO GO SPECT 201506 201506|201507 9 1 8 201506 201506|201508 9 6 2 201506 201506|201509 9 2 0 201507 201507|201508 11 7 4 201507 201507|201509 11 4 0

I have all of this fields, but i need to calculate in script SPECT field.

Can sameone help me.

• ###### Re: LOAD CALCULATE

Is this what you want? PFA

Best,

S

• ###### Re: LOAD CALCULATE

RESULT

SCRIPT

a:

DATE_FROM_DATE_TO,

IN_PORTFOLIO,

GO

//SPECT

FROM

(html, codepage is 1252, embedded labels, table is @1);

b:

if(DATE_FROM=peek(DATE_FROM), peek(SPECT) - GO, IN_PORTFOLIO - GO) as SPECT

Resident

a

order by

DATE_FROM,

DATE_FROM_DATE_TO;

• ###### Re: LOAD CALCULATE

You forgot!

Drop table a;

• ###### Re: LOAD CALCULATE

vero, grazie

• ###### Re: LOAD CALCULATE

and it the situation is it?

a:

LOAD * INLINE [

id, DATE_FROM, DATE_FROM_DATE_TO, IN_PORTFOLIO, GO
1, 201506, 201506|201507, 9 ,1
2, 201506, 201506|201508, 9 ,6
3, 201506, 201506|201508, 9 ,6
4, 201506, 201506|201508, 9 ,6
5, 201506, 201506|201509, 9 ,2
6, 201507, 201507|201508, 11, 7
7, 201507, 201507|201509, 11 ,4
]
;

• ###### Re: LOAD CALCULATE

Try:

DATE_FROM,

DATE_FROM_DATE_TO,

IN_PORTFOLIO,

GO,

rangesum(-GO, If(DATE_FROM=previous(DATE_FROM),peek('SPECT'),IN_PORTFOLIO)) as SPECT

FROM ....

• ###### Re: LOAD CALCULATE

thanks

and if the situation is this?

a:

LOAD * INLINE [

id, DATE_FROM, DATE_FROM_DATE_TO, IN_PORTFOLIO, GO
1, 201506, 201506|201507, 9 ,1
2, 201506, 201506|201508, 9 ,6
3, 201506, 201506|201508, 9 ,6
4, 201506, 201506|201508, 9 ,6
5, 201506, 201506|201509, 9 ,2
6, 201507, 201507|201508, 11, 7
7, 201507, 201507|201509, 11 ,4
]
;

• ###### Re: LOAD CALCULATE

No idea. You didn't say if SPECT can have negative values or that it cannot be less than zero. If it can't be less than zero try

rangemax(0,rangesum(-GO, If(DATE_FROM=previous(DATE_FROM),peek('SPECT'),IN_PORTFOLIO))) as SPECT

• ###### Re: LOAD CALCULATE
 ID DATE_FROM DATE_FROM_DATE_TO IN_PORTFOLIO GO SPECT 1 201506 201506|201507 9 1 8 2 201506 201506|201508 9 6 2 3 201506 201506|201508 9 6 2 4 201506 201506|201508 9 6 2 5 201506 201506|201509 9 2 0 6 201507 201507|201508 11 7 4 7 201507 201507|201509 11 4 0

This is the spect result for that situation...

• ###### Re: LOAD CALCULATE

Hi

Try like this