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.

[vishsaggi]

Need more information, based on what conditions your final column [Finally] is populated ?

[#3]

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

[Anonymous]

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

[#5]

Hi Stabdha!

Thank you for your reply. I understood the logic.

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

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

[#6]

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.

Next resident load again

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

[#7]

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

Raw:

LOAD * INLINE [

    Store, Item, Available

    S1, I1, 0

    S2, I1, 0

    S3, I1, 2

    S4, I1, 0

    S5, I1, 0

    T, I1, 2

];

RawTemp0:

load *,

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

Resident Raw;

RawTemp1:

load

Count(Store) as EmptyStores

Resident Raw

where Available = 0;

Let EmptyStores = peek('EmptyStores');

RawTemp2:

load

Available as Stock

Resident Raw

where Store = 'T';

Let Stock = peek('Stock');

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

Final:

load

*,

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;

[Anonymous]

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;

Hope this might help you .

Thanks,

Stabdha

[#9]

Thank you!

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

[#10]

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!