Distribute values between dates

[robert_mika]

I just reach an impasse.

I need split data at script level:

Example below

Inline table:(with Sundays Dates)

[ Date

.

.

.

01/05/16

08/05/16

15/05/16

22/05/16

29/55/16

05/06/16

.

.

.

]

Another table:

Start               End           value

02/05/16          31/05/16     29

I need to find a way to return table like this:

Date          Value

08/05/16    6   cause dates 02-07/05

15/05/16     7                         08-14/05

22/05/16      7                        15-21/05

29/05/16     7                         22-28/05

05/06/16     2                              because 29-7-7-7-6=2

The second table has more entities.

The one is just an example

[sunny_talwar]

What is the logic for dividing the Value? Is it one for each day?

[robert_mika]

Hi Sunny,

01/05 is Sunday

my first day is 02/05 which is Monday

following Sunday (08/05)  I need value if 6 (Mon-Sat)

next Sunday 15/05 I need a full week  (Sunday 08.05 till Saturday 14.05) -7 days

same for next 2 week

Last week on Sunday the 05.06 I need the value of 2 (for 29.05 and 30.05).

The Dates 02.05 and 31.05 are not being taken into calculations just dates between them.

I hope that makes sense

[fvelascog72]

Hi Robert,

I have found a solution but it´s a bit large.

I hope helps you.

Cheers.

[robert_mika]

Thank you, Federico

I have found a way using a while statement to split the data then summarize.

Distribute values from begin to and end contract date??

[effinty2112]

Hi Robert,

I've written a script that appears to get what you want. I've added an extra period

     Start, End, Value

    02/05/2016, 31/05/2016, 29

    01/06/2016, 30/06/2016, 29

and have an inline table of Sundays that extend before and after these periods.

A straight table gives this, WeekEnd 05/06/2016 appears twice because dates in week ending 05/06/2016 straddle the two periods.

PeriodID	WeekEnd	Sum(Cnt)
1	08/05/2016	6
1	15/05/2016	7
1	22/05/2016	7
1	29/05/2016	7
1	05/06/2016	2
2	05/06/2016	4
2	12/06/2016	7
2	19/06/2016	7
2	26/06/2016	7
2	03/07/2016	4

Here's the script:

Weeks:

LOAD * INLINE [

    WeekEnd

    01/05/2016

    08/05/2016

    15/05/2016

    22/05/2016

    29/05/2016

    05/06/2016

    12/06/2016

    19/06/2016

    26/06/2016

    03/07/2016

    10/07/2016

];

DayAdder:

LOAD * INLINE [

    DayAdder

    1

    2

    3

    4

    5

    6

    7

];

Join(Weeks)

LOAD * Resident DayAdder;

DROP Table DayAdder;

Dates:

LOAD

WeekEnd,

Date(WeekEnd - DayAdder) as Date

Resident Weeks;

Drop Table Weeks;

//Up to this point we've just fleshed out a simple calendar of WeekEnd dates and the dates that pertain

Periods:

Load

RowNo() as PeriodID,

*;

LOAD * INLINE [

    Start, End, Value

    02/05/2016, 31/05/2016, 29

    01/06/2016, 30/06/2016, 29

];

IntervalMatch(Date) LOAD Start, End Resident Periods;

Left Join([Periods-1])

LOAD 

1 as Counter,

*

Resident Periods;

Drop Table Periods;

Left Join(Dates)

LOAD

Counter,

Date,

PeriodID,

Value

Resident [Periods-1];

Drop Table  [Periods-1];

Result:

Load

*,

If(CumCounter <= Value,1,0) as Cnt;

Load

*,

If(PeriodID = Previous(PeriodID),

RangeSum(Counter, Peek('CumCounter')),1) AS CumCounter

  Resident Dates Where Counter =1 Order by Date;

DROP Table Dates;

It looks a bit messy and I'm sure it could be simplified.

Kind regards

Andrew