Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people,
I have the following question about using peek() or previous() function to give me a specified result. What I have is something like this:
Week | N_Orders | N_Planned | N_Stock | Total |
---|---|---|---|---|
Week1 | 500 | 640 | 500 | -640 |
Week2 | 500 | 640 | 500 | -640 |
Week3 | 500 | 640 | 500 | -640 |
The total value represents what left to be Planned wich formula is:
Total = N_Orders - N_Planned - N_Stock.
The following result is what I need to make:
Week | N_Orders | N_Planned | N_Stock | Total |
---|---|---|---|---|
Week1 | 500 | 640 | 500 | -640 |
Week2 | 500 | 640 | 0 | -140 |
Week3 | 500 | 140 | 0 | 360 |
The Stock need to become with the value 0 because I've just used all the Stock in the Order of the first Week (If in the first week and the following were 250, N_Stock column would be [500, 250, 0]). The Total value in the second Week is replaced by the negative value of the previous Total number.
N_Orders, N_Planned and N_Stock comes from 3 different tables and this numbers are from one single product. In my case I have multiple products.
How can I achieve my goals with qlikview? I've tried different variations of code with no sucess. Please, I need your help! I'll be in touch for explaining further more if needed.
Many Thanks, you guys are the greatest help!
Hi Marcus.
I'm sorry for the delay of my response. Here is how I resolved it:
Planeamento + Planear as A_Planear; |
Load *,
if(Necessity < 0, 0, Necessity) as ToPlan; |
Load *,
if | ||
( | ||
peek('Product')<>Artigo or isnull(peek('Product')), | ||
N_Orders - if(isnull(Qty_P_Total_temp), 0, Qty_P_Total_temp) - if(isnull(Qty_S_Total_temp), 0, Qty_S_Total_temp), | ||
Orders - Planned - Stock | ||
) as Necessity |
Load *,
if(Stock_Rem < 0, 0, Stock_Rem) as Stock; |
Load *, |
if | ||
( | ||
peek('Product)<>Product or isnull(peek('Product')), | ||
Qty_P_Total, | ||
(peek(N_Orders)-peek('Planned')-peek('Stock_Rem))*(-1) | ||
) as Planned; | ||
Load *,
N_Orders as Orders; |
Load *,
if | |||
( | |||
peek('Product')<>Artigo or isnull(peek('Product')), | |||
Qty_S_Total, | |||
(peek('Stock_Rem') - peek('N_Orders')) | |||
) as Stock_Rem; |
Many Thanks for the reply it helped
Have a look on this: Peek() vs Previous() – When to Use Each
- Marcus
I've read that article and read all about the function peek and previous in the help of Qlikview. After that I still couldn't resolve my problem.
Thanks Marcus.
I think the issue had not really a relation to peek/previous. Of course you could try it with some nested if loops to fetch all your conditions - but I believe it's more the question how looked the table from where is N_Stock is come from and how will it be matched with the other data.
- Marcus
Hi Marcus.
I'm sorry for the delay of my response. Here is how I resolved it:
Planeamento + Planear as A_Planear; |
Load *,
if(Necessity < 0, 0, Necessity) as ToPlan; |
Load *,
if | ||
( | ||
peek('Product')<>Artigo or isnull(peek('Product')), | ||
N_Orders - if(isnull(Qty_P_Total_temp), 0, Qty_P_Total_temp) - if(isnull(Qty_S_Total_temp), 0, Qty_S_Total_temp), | ||
Orders - Planned - Stock | ||
) as Necessity |
Load *,
if(Stock_Rem < 0, 0, Stock_Rem) as Stock; |
Load *, |
if | ||
( | ||
peek('Product)<>Product or isnull(peek('Product')), | ||
Qty_P_Total, | ||
(peek(N_Orders)-peek('Planned')-peek('Stock_Rem))*(-1) | ||
) as Planned; | ||
Load *,
N_Orders as Orders; |
Load *,
if | |||
( | |||
peek('Product')<>Artigo or isnull(peek('Product')), | |||
Qty_S_Total, | |||
(peek('Stock_Rem') - peek('N_Orders')) | |||
) as Stock_Rem; |
Many Thanks for the reply it helped