Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What is Qlikview equivalent of SAS Retain statement?

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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 ;

Not applicable
Author

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?

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

hi gwassenaar,

could you please have a look at http://community.qlik.com/message/266909#266909

it's related to this question.

.