topic Re: Interval optimisation in QlikView

Interval optimisation

loicdesse — Wed, 22 Aug 2018 14:58:43 GMT

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

Re: Interval optimisation

petter — Wed, 22 Aug 2018 17:15:47 GMT

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.

Re: Interval optimisation

loicdesse — Thu, 23 Aug 2018 08:13:13 GMT

Wow, thank you for your help

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

Re: Interval optimisation

petter — Mon, 27 Aug 2018 11:48:26 GMT

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

Re: Interval optimisation

loicdesse — Tue, 28 Aug 2018 13:39:43 GMT

Hi Petter,

Everything is ok, this solution is Perfect !

Re: Interval optimisation

petter — Wed, 29 Aug 2018 08:14:41 GMT

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