topic Re: Spreading according to qty in QlikView

Spreading according to qty

Anonymous — Tue, 03 Apr 2018 06:40:33 GMT

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

Re: Spreading according to qty

sunny_talwar — Tue, 03 Apr 2018 15:50:30 GMT

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;

Re: Spreading according to qty

Anonymous — Tue, 03 Apr 2018 17:14:38 GMT

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

Re: Spreading according to qty

sunny_talwar — Tue, 03 Apr 2018 19:09:39 GMT

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 *,

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;

Re: Spreading according to qty

Anonymous — Wed, 04 Apr 2018 05:03:48 GMT

I have changed the example- thanks