5 Replies Latest reply: Jun 1, 2016 4:40 PM by Andrew Walker RSS

    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

        • Re: Distribute values between dates
          Sunny Talwar

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

          • Re: Distribute values between dates
            Andrew Walker

            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)
            108/05/20166
            115/05/20167
            122/05/20167
            129/05/20167
            105/06/20162
            205/06/20164
            212/06/20167
            219/06/20167
            226/06/20167
            203/07/20164

             

            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