Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
loicdesse
Partner - Contributor II
Partner - Contributor II

Interval optimisation

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


1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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 '|');

  • First load the source intervals in line 22 to 26.
  • Then generate all days between the start and end dates for each SN by iterating with the WHILE clause and keep the number of shares that are on any given date in line 15 to 20.
  • Then sum up the total shares for each day in line 09 to 14.
  • Lastly sum up to get the maximum intervals with the same number of shares in line 01 to 08.

View solution in original post

5 Replies
petter
Partner - Champion III
Partner - Champion III

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 '|');

  • First load the source intervals in line 22 to 26.
  • Then generate all days between the start and end dates for each SN by iterating with the WHILE clause and keep the number of shares that are on any given date in line 15 to 20.
  • Then sum up the total shares for each day in line 09 to 14.
  • Lastly sum up to get the maximum intervals with the same number of shares in line 01 to 08.
loicdesse
Partner - Contributor II
Partner - Contributor II
Author

Wow, thank you for your help

I have to try this tomorrow, I'll let you know if everything is ok !

petter
Partner - Champion III
Partner - Champion III

Hi Loïc, did you get a chance to test it ... I am curious you know

loicdesse
Partner - Contributor II
Partner - Contributor II
Author

Hi Petter,

Everything is ok, this solution is Perfect !

petter
Partner - Champion III
Partner - Champion III

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