Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mansi_dv
Contributor
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
marcus_sommer
MVP & Luminary
MVP & Luminary

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