What is Qlikview equivalent of SAS Retain statement?

[#1]

Hi all,

I am having a bit of a problem in calculating the value of a field based on the previous value of another field. When I wirte the code below I get the succeeding table:

LOAD

                                                  *,

                                                  if(rowno()=1,

                                             standartStok,

                                             if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                          Previous(standartStok)-Previous(ol_qty), standartStok)

                                          ) as ATP,

ma_kod	desen	varyant	standartStok	ATP	ol_qty
ME1005-01	C11	1	3.460	3.460	600
ME1005-01	C11	1	3.460	2.860	400
ME1005-01	C11	1	3.460	3.060	600
ME1005-01	C11	1	3.460	2.860	100
ME1005-01	C11	1	3.460	3.360	100
ME1005-01	C11	1	3.460	3.360	400
ME1005-01	C11	1	3.460	3.060	200

As you can see that's wrong because I want the ATP to be reduced from previous value by the ol_qty at each line. When I try to do the following I get an error saying that ATP is not initiated.

LOAD

  *,

  if(rowno()=1,

      standartStok,

      if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

            Previous(ATP)-Previous(ol_qty), standartStok)

            ) as ATP,

I know why both pieces of code are wrong but I can't seem to figure out a way to make a circular reference here. In SAS I was able to do this by using the Retain statement. What do you suggest?

Thanks!

[Gysbert_Wassenaar · Accepted solution]

You'll need to use peek for ATP. See example:

[#3]

It worked and it actually solved my issue with duplicated rows when I used previous.

However, I don't understand the difference between the two expressions. This is from the reference manual:

peek( 'Sales' )

returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

so how come peek(ATP) works but previous(ATP) doesn't??Could you please explain?

Also I cannot use the fieldname ATP to calculate another field within the same LOAD statement. For example:

LOAD

                               ol_or_no2,ol_sr, ma_kod, desen, varyant, ol_qty,

                                        if(rowno()=1,

                                     standartStok,

                                             if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                          Peek(ATP)- Previous(ol_qty), standartStok)

                                           ) as ATP,

                                                  if(ATP >= ol_qty, now(),) as planDate

resident siparis;

returns an error saying that ATP field is not found. I managed to solve the problem by re-typing the expresion but isn't there a way to use ATP? Any idea on that?

and  Thanks a lot for  the answer.

[Gysbert_Wassenaar]

Peek gets the data from qlikviews associative database, but previous gets the data directly from the input source. That's why peek works here when previous doesn't. Trying to use ATP won't work because the other expressions also use the input source and not the associative database. What you could do is first store your table that includes ATP to a qvd file and then load that qvd and add your calculations that need ATP.