Spreading according to qty

[Anonymous]

Hi All,

I need to know if there is some clever way to spread qty between rows.

the solution should be in the script.

I have the following tables:

T1:                                                                T2:

Region    City          Forecast_Qty                Region          Qty_to_Spread

US          Chicago        10                             US                        15

US          NY                12                              DE                       10

US          Denver           5

DE          Berlin            12

According to the above tables I'll need that Berlin will get 10 since the qty in table T2 is higher than what forecasted in T1 for DE.

The problem is with the US, since they have  only 15 units I need to spread them from the highest forecast row to the lowest.

i.e. my final table will look like:

T3:                                                           

Region    City          Forecast_Qty       Spread Qty      

US          Chicago          10                        3

US          NY                  12                       12

US          Denver             5                         0

DE          Berlin              12                       10

Thanks for all the helpers

[sunny_talwar]

May be this

T1:

LOAD * INLINE [

    Region, City, Forecast Qty

    US, Chicago, 10

    US, NY, 12

    US, Denver, 5

    DE, Berlin, 12

];

Left Join (T1)

LOAD * INLINE [

    Region, Qty to Spread

    US, 15

    DE, 10

];

FinalTable:

LOAD *,

RangeMax(If(If(Region = Previous(Region), RangeSum(Peek('Forecast Qty New'), [Forecast Qty]), [Forecast Qty]) <= [Qty to Spread], [Forecast Qty], [Qty to Spread] - Alt(Peek('Forecast Qty New'), 0)), 0) as [Spread Qty],

If(Region = Previous(Region), RangeSum(Peek('Forecast Qty New'), [Forecast Qty]), [Forecast Qty]) as [Forecast Qty New]

Resident T1

Order By Region;

DROP Table T1;

[Anonymous]

Hi Roei,

Looking at you text "The problem is with the US, since they have  only 15 units I need to spread them from the highest forecast row to the lowest."

I would have expected NY to receive 10 units, Chicago 5 and Denver 0.

Why the deviating table???

Always allocating in groups of 5?

Greetings, Gilbert

[sunny_talwar · Accepted solution]

I see you changed the required output... you can change this to match the new output

T1:

LOAD * INLINE [

    Region, City, Forecast Qty

    US, Chicago, 10

    US, NY, 12

    US, Denver, 5

    DE, Berlin, 12

];

Left Join (T1)

LOAD * INLINE [

    Region, Qty to Spread

    US, 15

    DE, 10

];

FinalTable:

LOAD *,

RangeMax(If(If(Region = Previous(Region), RangeSum(Peek('Forecast Qty New'), [Forecast Qty]), [Forecast Qty]) <= [Qty to Spread], [Forecast Qty], [Qty to Spread] - Alt(Peek('Forecast Qty New'), 0)), 0) as [Spread Qty],

If(Region = Previous(Region), RangeSum(Peek('Forecast Qty New'), [Forecast Qty]), [Forecast Qty]) as [Forecast Qty New]

Resident T1

Order By Region, [Forecast Qty] desc;

DROP Table T1;

[Anonymous]

I have changed the example- thanks