Find previous value based on multiple fields

mansi_dv — Wed, 14 Feb 2018 06:34:59 GMT

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

Re: Find previous value based on multiple fields

marcus_sommer — Wed, 14 Feb 2018 12:29:36 GMT

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