...

1.18 Order-based computation: find ordinal number of the record containing the maximum value and perform cross-row computations

 
spl-cookbook(224)

Locate the record containing the maximum value, retrieve it and perform cross-row computations. The task is to calculate the growth rate of the date with the highest closing price in SSE Composite Index compared with the previous date based on the following stock transaction table.

Date Open Close Amount
2019/12/31 3036.3858 3050.124 2.27E11
2019/12/30 2998.1689 3040.0239 2.67E11
2019/12/27 3006.8517 3005.0355 2.58E11
2019/12/26 2981.2485 3007.3546 1.96E11
2019/12/25 2980.4276 2981.8805 1.9E11
… … … …

To do the task, we need to find ordinal number of the record containing the highest closing price and then compare with that of the previous date to get the result.

SPL script:

A
1 =T(“000001.csv”)
2 =A1.sort(Date)
3 =A2.pmax(Close)
4 =A2.calc(A3,Close/Close[-1]-1)

A1 Import the original data file;
A2 Sort by date;
A3 Find ordinal number of the member containing the highest stock price;
A4 Calculate the growth rate using the closing price of the current date and that of the previous date.

Similarly, we use pmin function to get ordinal number of the member containing the minimum value:

A
3 =A3.pmin(Close)

A3 Find ordinal number of the member containing the lowest stock price.

There may be more than one record holding the maximum value. To return ordinal numbers of all eligible records, use @a option in pmax function:

A
3 =A2.pmax@a(Close)
4 =A2.calc(A3,Close/Close[-1]-1)

A3 Find ordinal numbers of all records containing the highest stock price;
A4 Calculate the growth rate using the closing price of the current date and that of the previous date.

To locate the eligible record backwards, use @z option in pmax function:

A
3 =A2.pmax@z(Close)

A3 Find ordinal number of the member containing the highest stock price backwards.
 

spl-cookbook(224)
 
 •  154 View  •