RaqForum 25 No.
301 View  • 
     ...

6.8 Alignment grouping by ordinal number: keep all matching members for each group

 
spl-cookbook(224)

Sort data by the order of specified ordinal numbers and keep all matching members for each group. This is suitable for computing scenarios where we want to know information of all members in each group or where we need to use these member records to perform further statistical analysis.

List total sales amount in each month the year 2014 based on the sales table. Below is part of the table:

ID CustomerID OrderDate Amount
10248 VINET 2013/07/04 2440
10249 TOMSP 2013/07/05 1863.4
10250 HANAR 2013/07/08 1813.0
10251 VICTE 2013/07/08 670.8
10252 SUPRD 2013/07/09 3730.0
… … … …

SPL provides @a option to use in A.align(n,y) function to keep all matching members for each group during an alignment grouping operation.

SPL script:

A
1 =T(“Sales.csv”)
2 =A1.select(year(ORDERDATE)==2014)
3 =A2.align@a(12,month(ORDERDATE))
4 =A3.new(#:Month,~.sum(AMOUNT):AMOUNT)

A1 Query Sales table.
A2 Select records of the year 2014 from the Sales table.
A3 Use A.align@a(n,y) function to divide records of Sales table into 12 groups according to the order of months from January to December; @a option enables keeping all matching members for each group.
A4 Calculate total sales amount in each month.

Execution result:

Month Amount
1 66692.8
2 52207.2
3 39979.9
4 60699.39
… …
spl-cookbook(224)
 
 •  301 View  •