...

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


 
 •  225 View  •