Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Need more information, based on what conditions your final column [Finally] is populated ?
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
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
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';
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
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;
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
Thank you!
The only thing, we have to keep the available 2 items for store S3. But in Final column it changed to 0
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!