Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

What is Qlikview equivalent of SAS Retain statement?

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_koddesenvaryantstandartStokATPol_qty
ME1005-01C1113.4603.460600
ME1005-01C1113.4602.860400
ME1005-01C1113.4603.060600
ME1005-01C1113.4602.860100
ME1005-01C1113.4603.360100
ME1005-01C1113.4603.360400
ME1005-01C1113.4603.060200

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!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: What is Qlikview equivalent of SAS Retain statement?

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


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: What is Qlikview equivalent of SAS Retain statement?

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


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: What is Qlikview equivalent of SAS Retain statement?

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.

MVP & Luminary
MVP & Luminary

Re: What is Qlikview equivalent of SAS Retain statement?

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.


talk is cheap, supply exceeds demand