Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I have some difficulties to solve an issue today and I'd like you to have a look at it.
The table I'm working with gives me : stakeholder number (SN), nb of shares (NB), acquisition date (start), date of transfer. (end)
For example :
SN | nb | start | end
0001 | 15 | 01/01/2017 | 31/12/2017
0001 | 10 | 01/06/2017 | 31/12/2018
I'd like to transform this table to a new one, with real date interval :
SN | start | end | nb
0001 | 01/01/2017 | 31/05/2017 | 15
0001 | 01/06/2017 | 31/12/2017 | 25
0001 | 01/01/2018 | 31/12/2018 | 10
0001 | 01/01/2019 | - | 0
If you have an idea about this, please let me know
Thank you !!
This is a rather straightforward way of getting the intervals:
FINAL:
LOAD
SN,
nb,
Date(Min(Date)) AS start,
Date(Max(Date)) AS end
GROUP BY
SN, nb;
LOAD
SN,
Date,
Sum(nb) AS nb
GROUP BY
SN,Date;
LOAD
SN,
nb,
Date(start + IterNo()-1) AS Date
WHILE
IterNo() <= (end-start)+1;
// The following five lines can of course be turned into a LOAD ... FROM or a preceding SQL statement
LOAD * INLINE [
SN | nb | start | end
0001 | 15 | 01/01/2017 | 31/12/2017
0001 | 10 | 01/06/2017 | 31/12/2018
] (delimiter is '|');
This is a rather straightforward way of getting the intervals:
FINAL:
LOAD
SN,
nb,
Date(Min(Date)) AS start,
Date(Max(Date)) AS end
GROUP BY
SN, nb;
LOAD
SN,
Date,
Sum(nb) AS nb
GROUP BY
SN,Date;
LOAD
SN,
nb,
Date(start + IterNo()-1) AS Date
WHILE
IterNo() <= (end-start)+1;
// The following five lines can of course be turned into a LOAD ... FROM or a preceding SQL statement
LOAD * INLINE [
SN | nb | start | end
0001 | 15 | 01/01/2017 | 31/12/2017
0001 | 10 | 01/06/2017 | 31/12/2018
] (delimiter is '|');
Wow, thank you for your help
I have to try this tomorrow, I'll let you know if everything is ok !
Hi Loïc, did you get a chance to test it ... I am curious you know
Hi Petter,
Everything is ok, this solution is Perfect !
Thanks - fun to create it - exciting to see how useful the preceding load functionality can be used to very efficiently create non-overlapping intervals and summing a measure for each....