LOAD CALCULATE

[#1]

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.

[sunny_talwar]

Is this what you want? PFA

Best,

S

[maxgro · Accepted solution]

RESULT

SCRIPT

a:

LOAD DATE_FROM,

     DATE_FROM_DATE_TO,

     IN_PORTFOLIO,

     GO

     //SPECT

FROM

[http://community.qlik.com/thread/150786]

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

b:

load * ,

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

Resident

  a

order by

  DATE_FROM,

    DATE_FROM_DATE_TO;

[#4]

You forgot!

Drop table a;

regads!

[maxgro]

vero, grazie

[Gysbert_Wassenaar]

Try:

LOAD

     DATE_FROM,

     DATE_FROM_DATE_TO,

     IN_PORTFOLIO,

     GO,

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

FROM ....

[#7]

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
];

[#8]

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
];

[Gysbert_Wassenaar]

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

[#10]

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

can you please help me?