14 Replies Latest reply: Jul 27, 2016 7:40 PM by Iskander Smagulov

# Take a value from one row and put into another

Hi guys!

The situation:

I have table 1

StoreItemAvailableChecked

S1

I10*
S2I10*
S3I12
S4I10*
S5I10
TI12

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:

StoreItemAvailableCheckedFinally

S1

I10*1
S2I10*1
S3I122
S4I10*0
S5I100
TI120

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

• ###### Re: Take a value from one row and put into another

The final column calculated in such way:

1) It finds 0 values in column Available and sign '*' in column Checked. Let's call them 'Wanted'

2) Looks for values in store T to put them into 'Wanted' stores

3) We can put maximum 1 item into each store.

So, we have 'Wanted' stores - S1,S2,S4

We have available items in store T equal to 2.

We need to spread  the values from T to S1,S2,S4.

So we take 1 from T to S1, we take 1 from T to S2. Then we see that T is empty. We finish spreading process.

Finally, we have:

S1-1

S2-1

S3-2

S4-0

S5-0

T-0

• ###### Re: Take a value from one row and put into another

Hi Iskander,

You can do this :

Let t_Count = Load Available resident Table1 where Store = 'T';

Let vIter = Load count(Checked) resident Table1 where Checked = 1;

A:

for i = 1 to vIter

Load *, 1 as Finally resident Table1 where Checked =1 and \$(t_Count) >0;

Let t_Count = t_Count-1;

Next i

Concatenate

Load *, 0 as Finally resident Table1 where Checked =1 and Finally <>1;

Hope this works.

Thanks,

Stabdha

• ###### Re: Take a value from one row and put into another

Hi Stabdha!

The only thing is that qlikview drops the syntax error in the row -

Let t_Count = Load Available resident Table1 where Store = 'T';

• ###### Re: Take a value from one row and put into another

Iskander, what you can do is

Temp :

Load Available resident Table1 where Store = 'T';

Let t_Count = Peek('Available' , -1, 'Temp');

and then

Drop table Temp;

Thanks,

Stabdha

• ###### Re: Take a value from one row and put into another

Thank you! I'll try!

• ###### Re: Take a value from one row and put into another

Hi Islander,

First peek the value from T keep this in a variable var1.

Next resident load the table with an if saying

If(condition=* and available =0), var2 =var2 +1,

0 as flag.

This will give you a variable var2 with no of stores you want to divide the value of T.

This time

For I = 0 to var1

peek (third table, rowno() = i

If(condition=* and available =0) and flag <var1

then finally = flag+1 and flag=flag+1;

Var1 =var1- 1;

You have to repeat the for loop if var1 > count(*) in the table.

Sorry I cannot give you the exact code, but this will give you some idea, if you can share a qvw, I may check the feasibility of my pseudo code too.

Regards

• ###### Re: Take a value from one row and put into another

Thank you! Great explanation. But hard to make it.. I have a lot of syntax errors unfortunately.

I attached the qvw, so you can try to make it.

Thank you again for help!

• ###### Re: Take a value from one row and put into another

You may want to try this. Final is the result table.

Raw:

Store, Item, Available

S1, I1, 0

S2, I1, 0

S3, I1, 2

S4, I1, 0

S5, I1, 0

T, I1, 2

];

RawTemp0:

If(Available = 0,1,0) as Checked

Resident Raw;

RawTemp1:

Count(Store) as EmptyStores

Resident Raw

where Available = 0;

Let EmptyStores = peek('EmptyStores');

RawTemp2:

Available as Stock

Resident Raw

where Store = 'T';

Let Stock = peek('Stock');

let StockLeft = if(\$(Stock) - \$(EmptyStores)<0,0,\$(Stock) - \$(EmptyStores));

Final:

*,

numsum(Checked,peek(Accu_Check,-1)) as Accu_Check,

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

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

) as Final

resident RawTemp0;

drop table Raw, RawTemp0, RawTemp1, RawTemp2;

• ###### Re: Take a value from one row and put into another

Thank you!

The only thing, we have to keep the available 2 items for store S3. But in Final column it changed to 0

• ###### Re: Take a value from one row and put into another

Try this..

T1:
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:
If(Store='T',0,
if(Available=0 and Checked='*' and RangeSum(1, Peek('Final'))<=\$(vAvailable),1,Available)) as Final
Resident T1;

DROP Table T1;

• ###### Re: Take a value from one row and put into another

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)

• ###### Re: Take a value from one row and put into another

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

• ###### Re: Take a value from one row and put into another

Thank you for help! This works for me!