Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am wondering how to change the value of a field based on the value of a previous line in the table. The folowing table has been created from joining an order table with the inventory table and adding some conditions and a group by statement:
ordnum itemnum design ordqty balance_on_hand
189 ME1005 105 1000 10,000
202 ME1005 105 3000 10,000
305 ME1005 105 1000 10,000
407 ME1005 05 500 10,000
However I would like second line the balance_on_hand to be equal to the value in first line - the ordqty in the first line. The resulting table should look like:
ordnum itemnum design ordqty balance_on_hand
189 ME1005 105 1000 10,000
202 ME1005 105 3000 9,000
305 ME1005 105 1000 6,000
407 ME1005 05 500 5,500
In SAS I was able to do this using the Retain statement. Is there an equivalent? Could someone share with me an example? Btw I am using the personal edition so please post the code in your reply.
Thanks.
Ok, let's try this. It assumes that the balance is only for itemnum and that the design doesn't matter for the calculation. Also that the table is grouped by itemnum.
if(rowno()=1,
balance_on_hand,
if(itemnum=peek(itemnum),
peek(balance_on_hand)-peek(ordqty)
,balance_on_hand
)
) as balance_on_hand
Assuming your table is ordered correctly already something like:
if(rowno()=1,balance_on_hand,peek(balance_on_hand)-peek(ordqty)) as balance_on_hand
you may need to expand the condition of the if statement to check for changes in the columns you grouped by.
But it's probably easier done with expresions in charts using for example rangesum
Well maybe I've oversimplified the question:
Obviously the company sells more than 1 item with 1 design: Assume the initial table is:
ordnum itemnum design ordqty balance_on_hand
189 ME1005 105 1000 10,000
192 ME1010 122 5000 25,000
202 ME1005 105 3000 10,000
225 ME1010 122 3000 25,000
305 ME1005 105 1000 10,000
407 ME1005 105 500 10,000
Resulting table should be
ordnum itemnum design ordqty balance_on_hand
189 ME1005 105 1000 10,000
192 ME1010 122 5000 25,000
202 ME1005 105 3000 7,000
225 ME1010 122 3000 20,000
305 ME1005 105 1000 6,000
407 ME1005 105 500 5,500
I would first sort by itemnum and design but I still need to identify the first occurance of that item number and design rather than the row number.
Any ideas on how to do that?
Ok, let's try this. It assumes that the balance is only for itemnum and that the design doesn't matter for the calculation. Also that the table is grouped by itemnum.
if(rowno()=1,
balance_on_hand,
if(itemnum=peek(itemnum),
peek(balance_on_hand)-peek(ordqty)
,balance_on_hand
)
) as balance_on_hand
Sounds like it should work but I've tried and still getting the same thing repeating:
Here is my code:
Could you see where I may be making a mistake??
And thanks a lot I really appreciate your help. (scroll down to stoksip: label to see the code)
ODBC CONNECT32 TO RAPOR;
siparis:
SQL SELECT ol_or_no1, ol_or_no2, ol_sr, ol_ma_kod AS ma_kod, ol_qty, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, ol_wrk_basdt, ol_onaydt,
ol_or_anhno, ol_onay2, ol_onay2dt,or_pe_no, or_anhno, or_rr_no
FROM DBA.ordln
INNER JOIN DBA.ord
ON ordln.ol_or_anhno = ord.or_anhno
WHERE ol_kapa = 'A' AND ol_or_no1 = 2012 AND ol_ma_kod = 'ME1005-01' /*AND ol_onay2 <> ' */
order by ma_kod, desen, varyant
;
stok:
Load
*,
brutStok-standartStok as kMetrajStok;
SQL SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant,
sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,
sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt
WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt
ELSE 0*a.sh_qty_mt END)as standartStok
FROM DBA.stkhar AS a
INNER JOIN DBA.mam AS b
ON a.sh_ma_kod = b.ma_kod
WHERE a.sh_ma_kod ='ME1005-01' AND a.sh_sf_no1 = 2012/* AND a.sh_ds_kod = '122'*/ AND a.sh_sf_ar_no = 20
GROUP BY a.sh_ma_kod, a.sh_ds_kod, a.sh_vry
order by ma_kod, desen, varyant
;
stoksip:
Join (siparis) load *,
if(rowno()=1,
standartStok,
if(ma_kod=peek(ma_kod) AND desen = peek(desen) and varyant = peek(varyant) ,
peek(standartStok)-peek(ol_qty), standartStok)
) as ATP
resident stok ;
I'ive actually tried the same thing with previous instead of the peek and it's telling me it cannot find ol_qty. I guess b/c it's in the sparis table not in stok table.
Any idea how I can fix that?
I think you need to join siparis and stok first. Or change the select statement of stok so it includes the ol_qty column. But as I mentioned before it may be easier to work with expressions in charts/pivot tables instead.
yea I figured it out. I am pretty new to qlikview so I do a lot of trial erros before I get the right syntax but your suggestıon of the peek worked fine.
Thanks a lot
hi gwassenaar,
could you please have a look at http://community.qlik.com/message/266909#266909
it's related to this question.
.