# Generate Calculated Columns Based on the Aggregation Results

## Examples

### Example1

The Excel file book1.xlsx has three columns, as shown in the figure below, in which Master10 is 10-digit numbers, and Digit8 is the first eight digits of Master10.

The task is to calculate the value of Ismax column following the rule that: classify the data according to Digit8; if Master10 is the maximum value in the category, the value of Ismax is Master10; otherwise, the value is Nil.

Write SPL script:

 A 1 =T("e:/work/book1.xlsx") 2 =A1.group(Digit8).run(a=~.max(Master10),~.run(Ismax=if(Master10==a,Master10,"Nil"))) 3 =T("e:/work/book2.xlsx",A1)

A1 Read the data of book1.xlsx

A2 Group the data by Digit8, and then loop through each group. First, calculate the maximum value a of Master10 in the group, and then loop through each record in the group. If Master10=a in this record, set Ismax=Master10, otherwise Ismax=Nil

A3 Store the results in A1 to book2.xlsx

### Example2

There is an Excel file book1.xlsx as shown below:

The original data is out of order. We need to find a record with the latest date (DATE) in each group (FRUIT), mark it as TRUE, and mark the others as FALSE. The results are as follows:

Write SPL script:

 A 1 =T("e:/work/book1.xlsx") 2 =A1.group(FRUIT).run(a=~.max(DATE),~.run(‘LATEST DATE’=if(DATE==a, "TRUE","FALSE"))) 3 =T("e:/work/book2.xlsx",A1)

A1 Read the data of book1.xlsx

A2 Group the data by FRUIT, and then loop each group. First, calculate the maximum value a of DATE in the group, and then loop each record in the group. If DATE=a in this record, set LATEST DATE=TRUE, otherwise LATEST DATE=FALSE

A3 Store the results in A1 to book2.xlsx