Find previous value based on multiple fields

[mansi_dv]

Hi All,

Following is my query:

I have the following stored in my qvx:

load * inline[

Account, TransactionId, UniqueValue

1, 111, 'abc'

10, 120, 'bcd'

2, 112, 'def'

10, 117, 'stu'

2, 113, 'ghi'

3, 114, 'jkl'

3, 116, 'pqr'

10, 118, 'vwx'

3, 115, 'mno'

10, 119, 'yza'

];

I want to display a table such that I get a previous value of UniqueValue coulmn based on ascending order of TransactionId sorted by Account like:

Account, TransactionId, UniqueValue, LastUniqueValue

1, 111, abc, -

2, 112, def, -

2, 113, ghi, def

3, 114, jkl, -

3, 115, mno, jkl

3, 116, pqr, mno

10, 117, stu, -

10, 118, vwx, stu

10, 119, yza, vwx

10, 120, bcd, yza;

Please let me know if anyone can create a script expression or a chart function to achieve this.

Thanks,

Mansi

[marcus_sommer]

You could use an interecord-function like Peek() or Previous() ? for it in a properly sorted resident load, maybe like:

load

     *, if(Account = previous(Account), previous(UniqueValue)) as LastUniqueValue

resident Source order by Account, TransactionId;

- Marcus