distribute the field values in different rows

[Anonymous]

Hi experts!

I need your help in data structuring. I have a table in that total Assortment Qty is given just in one row of SKUStore Key. I want to evenly distribute it in other rows if items are already allocated. Please find the current and to be tables below. Could you help me in achieving to be data table from current model.

Current Data Model
CartonNumber	SkuStore	Assortment Qty	Assortment Qty to Display	Allocated Qty	Unallocated Qty
839R	6058	2	2	2	0
840R	6058	0	2	0
566R	1694	2	2	2	0
567R	1694	0	2	0
570R	276694	0	4	0
571R	276694	0	4	0
	276694	4	4	2	2

Proposed Data Model
CartonNumber	SkuStore	Assortment Qty	Assortment Qty to Display	Allocated Qty	Unallocated Qty
839R	6058	1	2	1	0
840R	6058	1	2	1	0
566R	1694	1	2	1	0
567R	1694	1	2	1	0
570R	276694	1	4	1	0
571R	276694	1	4	1	0
	276694	2	4	0	2
Attached is the spread sheet.

Thanks!

[sunny_talwar]

Try this script:

Table:

LOAD CartonNumberKey,

    SkuStoreKey,

    [Assortment Qty],

    [Assortment Qty to Display],

    [Allocated Qty],

    [Unallocated Qty]

FROM

Community_165549.xlsx

(ooxml, embedded labels, table is Sheet2);

Join(Table)

LOAD SkuStoreKey,

  Count(SkuStoreKey) as Count

Resident Table

Group By SkuStoreKey;

Table1:

NoConcatenate

LOAD CartonNumberKey,

    SkuStoreKey,

    [Assortment Qty to Display],

    [Unallocated Qty],

    If([Allocated Qty] = 0, 1, [Allocated Qty] - Count + 1) as [Allocated Qty],

  If([Assortment Qty] = 0, 1, [Assortment Qty] - Count + 1) as [Assortment Qty]

Resident Table

Order By SkuStoreKey, [Assortment Qty] desc;

DROP Table Table;

Output:

[sunny_talwar · Accepted solution]

Application attached: