Updating by row position in a grouped table

Hello, I have a table grouped by instrument:

instrument TR

-----------------

AU200_AUD 66.1

AU200_AUD 114.1

AU200_AUD 72.9

AU200_AUD 121.3

AU200_AUD 83.15

AUD_CAD 0.00975

AUD_CAD 0.01005

AUD_CAD 0.00435

AUD_CAD 0.00755

AUD_CAD 0.00748

AUD_CHF 0.026975

AUD_CHF 0.011215

AUD_CHF 0.007245

AUD_CHF 0.01373

AUD_CHF 0.010555

AUD_JPY 0.8835

AUD_JPY 1.1165

AUD_JPY 1.0055

AUD_JPY 1.3975

AUD_JPY 1.6915


I want to update TR so TR:TR*-1 in every row after the third one per instrument:


instrument TR

-----------------

AU200_AUD 66.1

AU200_AUD 114.1

AU200_AUD 72.9

AU200_AUD -121.3

AU200_AUD -83.15

AUD_CAD 0.00975

AUD_CAD 0.01005

AUD_CAD 0.00435

AUD_CAD -0.00755

AUD_CAD -0.00748

AUD_CHF 0.026975

AUD_CHF 0.011215

AUD_CHF 0.007245

AUD_CHF -0.01373

AUD_CHF -0.010555

AUD_JPY 0.8835

AUD_JPY 1.1165

AUD_JPY 1.0055

AUD_JPY -1.3975

AUD_JPY -1.6915


But I don’t know how to do it.


Please can you help me?


Thanks


Francisco

groups can have different number of rows

HI Francisco,

This should do it.

Regards,

Paul

q)update neg TR from t where 2 < (rank;i) fby instrument

instrument TR

--------------------

AU200_AUD 66.1

AU200_AUD 114.1

AU200_AUD 72.9

AU200_AUD -121.3

AU200_AUD -83.15

AUD_CAD 0.00975

AUD_CAD 0.01005

AUD_CAD 0.00435

AUD_CAD -0.00755

AUD_CAD -0.00748

AUD_CHF 0.026975

AUD_CHF 0.011215

AUD_CHF 0.007245

AUD_CHF -0.01373

AUD_CHF -0.010555

AUD_JPY 0.8835

AUD_JPY 1.1165

AUD_JPY 1.0055

AUD_JPY -1.3975

AUD_JPY -1.6915

Many Thanks