Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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