Take a value from one row and put into another

[#1]

Hi guys!

Very interesting question, I couldn't find the answer in google yet. Please help or give an advice.

The situation:

I have table 1

Store	Item	Available	Checked
S1	I1	0	*
S2	I1	0	*
S3	I1	2
S4	I1	0	*
S5	I1	0
T	I1	2

The task is to take Available values from store T and spread it into other Checked stores. It should spread only 1 item for each necessary store.

Finally table would look like:

Store	Item	Available	Checked	Finally
S1	I1	0	*	1
S2	I1	0	*	1
S3	I1	2		2
S4	I1	0	*	0
S5	I1	0		0
T	I1	2		0

Also please help to make new row where I could write from which store I took item and where I put this item.

[settu_periasamy]

Try this..

T1:
LOAD * INLINE [
Store, Item, Available, Checked
S1, I1, 0, *
S2, I1, 0, *
S3, I1, 2,
S4, I1, 0, *
S5, I1, 0,
T, I1, 2,
];


temp:
Load Available Resident T1 where Store = 'T';
Let vAvailable = peek('Available');
DROP Table temp;

NoConcatenate
Final:
LOAD Store,Item,Available,Checked,
If(Store='T',0,
if(Available=0 and Checked='*' and RangeSum(1, Peek('Final'))<=$(vAvailable),1,Available)) as Final
Resident T1;

DROP Table T1;

[#3 · Accepted solution]

Hi, then can modify in this way.

Previous:

if(Store='T',$(StockLeft),

  if(Available = 0 and numsum(Checked,peek(Accu_Check,-1))<=$(Stock),1,0)

) as Final

Now:

if(Store='T',$(StockLeft),

  if(Available = 0,

               if(numsum(Checked,peek(Accu_Check,-1))<=$(Stock),1,0),

     Available)

) as Final

[#4]

Thank you! Fot this example it works!

But in case when there is number if items in Stock are more than in all zero Stocks, it should keep some remained items (items in stock - count of stocks with 0 values)

[#5]

Thank you! I'll try!

[#6]

Thank you for help! This works for me!