Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Is this what you want? PFA
Best,
S
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;
You forgot!
Drop table a;
regads!
vero, grazie
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 ....
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
];
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
];
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
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?