Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

LOAD CALCULATE

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: LOAD CALCULATE

RESULT

1.jpg

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;

11 Replies

Re: LOAD CALCULATE

Is this what you want? PFA

Best,

S

MVP
MVP

Re: LOAD CALCULATE

RESULT

1.jpg

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;

Not applicable

Re: LOAD CALCULATE

You forgot!

Drop table a;

regads!

MVP
MVP

Re: LOAD CALCULATE

vero, grazie

Re: LOAD CALCULATE

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


talk is cheap, supply exceeds demand
Not applicable

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

Not applicable

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


talk is cheap, supply exceeds demand
Not applicable

Re: LOAD CALCULATE

ID

DATE_FROMDATE_FROM_DATE_TOIN_PORTFOLIOGOSPECT
1201506201506|201507918
2201506201506|201508962
3201506201506|201508962
4201506201506|201508962
5201506201506|201509920
6201507201507|2015081174
7201507201507|2015091140

This is the spect result for that situation...

can you please help me?

Community Browser