Updating by row position in a grouped table

Community Support
1

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

2

groups can have different number of rows

3

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

4

Many Thanks