Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

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


Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Interval optimisation

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

Re: Interval optimisation

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

Partner
Partner

Re: Interval optimisation

Wow, thank you for your help

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

MVP
MVP

Re: Interval optimisation

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

Partner
Partner

Re: Interval optimisation

Hi Petter,

Everything is ok, this solution is Perfect !

MVP
MVP

Re: Interval optimisation

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