Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mansi_dv
New Contributor

Find previous value based on multiple fields

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

1 Reply
MVP & Luminary
MVP & Luminary

Re: Find previous value based on multiple fields

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