Binary operations

  Set operations (File comparison)

  Here are f1.txt and f2.txt (Sample data is listed below). Their first lines are column names. Task: calculate intersection between their Name fields.

  f1.txt:

Name  Dept Rachel  Sales Ashley  R&D Matthew Sales Alexis  Sales Megan  Marketing

 

  f2.txt:

Name  Dept Emily  HR Ashley  R&D Matthew  Sales Alexis  Sales Megan  Marketing

 

  SPL script:

	A	B
1	=file("E:\\f1.txt").import@t()	=file("E:\\f2.txt").import@t()
2	=[A1.(Name),B1.(Name)].isect()

 

  isect() function calculates intersection of two sets. A1.(Name) retrieves A1’s Name column and return it as a sequence; B1.(Name) retrieves B1’s Name column and return it as a sequence. Below is the final result:

  

  Similarly, we use union() function to calculate union, diff() function to calculate difference, and conj() function to calculate concatenation (which is equivalent to union all). You can also use operators to generate more concise statement, as shown below:

  A1.(Name) ^ B1.(Name)

  A1.(Name) & B1.(Name)

  A1.(Name) \ B1.(Name)

  A1.(Name) | B1.(Name)

  In the above example, the whole text file is imported and automatically split into multiple fields and one field is retrieved for calculating intersection. To compare lines of data, just add @s option to import the text file as a single-column table sequence where the column name is Name(Tab)Dept, which contains the invisible character tab. The column name is illegal and can’t be referenced directly. In this case, we can use numbers to represent the column names to write the following code:

	A	B
1	=file("D:\\f1.txt").import@ts()	=file("D:\\f2.txt").import@ts()
2	=A1.(#1)^B1.(#1)

 

  A convenient alternative is to return only the single-column detailed data as a set (sequence) by adding @i option. This way you can directly calculate the intersection with A1^B1:

	A	B
1	=file("D:\\f1.txt").import@tis()	=file("D:\\f2.txt").import@tis()
2	=A1^B1

 

  The two algorithms have same results:

  

 

  Merge

  Below are two text files – table1.txt and table2.txt, sorted by logical primary key ID1 and ID2 respectively. Task: Update table1 with data in table2 according to the primary keys. Rule: Replace a record’s field values in table1 with values in the corresponding record in table2 if their primary key values are same, and insert the table2’s record to table1 if their primary key values are different.
  Source files:

Table1	Table2
ID1 ID2 Col1 Col2 2  B1  2B1  row 2  B2  2B2  row 3  B1  3B1 4  B3  4B3  Row	ID1  ID2  Col1 Col2 1  B1  diff  row 2  B1  diff  row 2  diff  diff  row 3  B1  3B1

 

  Here’s the updated table1:

ID1	ID2	Col1	Col2
1	B1	diff	row
2	B1	diff	row
2	B2	2B2	row
2	diff	diff	row
3	B1	3B1
4	B3	4B3	row

 

  SPL script:

	A	B
1	=file("D:\\table1.txt").cursor@t()	=file("D:\\table2.txt").cursor@t()
2	=[B1,A1].mergex@u(ID1,ID2)
3	=file("D:\\result.txt").export@t(A2)

 

  Read in the two files as cursors and update A1 with B1 by their logical primary keys. mergex() function merges multiple data sets while keeping the result set in order; @u option enables calculating their union. The merging result will be exported to a target file.

  Cursors can handle files of any size. If a file is unordered, you need to sort it first by adding a sorting expression, as shown below:

  [B1.sortx(ID1,ID2),A1.sortx(ID1,ID2)].mergex@u(ID1,ID2)

  Order-based set operations

  Both files below are ordered by Name and Dept. Task: calculate their intersection.

  f1.txt:

Name  Dept Alexis  Sales Ashley  R&D Matthew Sales Megan  Marketing Rachel  Sales

 

  f2.txt:

Name  Dept Alexi  Sales Ashley  R&D Emily  HR Matthew Sales Megan  Marketing

 

  This is an order-based set operation. We use merge() function to calculate the intersection, which is more efficient. Below is SPL script:

	A	B
1	=file("D:\\f1.txt").import@t()	=file("D:\\f2.txt").import@t()
2	=[B1,A1].merge@i(Name,Dept)
3	=file("D:\\result.txt").export@t(A2)

 

  merge() function merges table sequences. It works with @i option to calculate intersection, @u option to calculate union, and @d option to calculate difference.

  Here’s the final result:

  

  Sort files with sort() function if they are unordered. Note that sorting and merging small files is slower than processing them with common set-operation functions. merge() is more suitable for handling large files.

  Association operations

  emp.txt is a tab separated text file, where EId field points to sales.txt’s SellerId field. Task: Align emp.txt’s Dept field, Name field and Gender field to sales.txt.

  emp.txt:

EId	State	Dept	Name	Gender	Salary	Birthday
2	New York	Finance	Ashley	F	11000	1980/07/19
3	New Mexico	Sales	Rachel	F	9000	1970/12/17
4	Texas	HR	Emily	F	7000	1985/03/07
5	Texas	R&D	Ashley	F	16000	1975/05/13
6	California	Sales	Matthew	M	11000	1984/07/07
7	Illinois	Sales	Alexis	F	9000	1972/08/16
8	California	Marketing	Megan	F	11000	1979/04/19

 

  SPL script:

	A
1	=sOrder=file("D:\\sales.txt").import@t()
2	=emp=file("D:\\emp.txt").import@t(EId,Name,Dept,Gender)
3	=join@1(sOrder:s,SellerId;emp:e,EId)
4	=A3.new(s.OrderID,   s.Client, s.SellerId,  s.Amount,   s.OrderDate, e.Name, e.Dept,   e.Gender)

 

  join() function performs a left join where two tables are renamed s and e respectively. @l enables a left join and @f enables a full join. By default the function performs an inner join. A4 retrieves desired fields from the joining result to form a new two-dimensional table, as shown below:

  

Complex calculations

  Multilevel association

  In the following diagram, there are 5 data files. Orders is the fact table and the other four are dimension tables. Task: Find orders where the customer and the supplier are in the same region, group those orders by cities, and count orders and sum the orders amount for each city.

  The tables’ relationships are as follows:

  

  SPL script:

	A
1	=file("D:/files/orders.txt").import@t()
2	=file("D:/files/customer.txt").import@t()
3	=file("D:/files/product.txt").import@t()
4	=file("D:/files/supplier.txt").import@t()
5	=file("D:/files/region.txt").import@t()
6	=A2.switch(city,A5:city)
7	=A4.switch(city,A5:city)
8	=A3.switch(sid,A4:sid)
9	=A1.switch(pid,A3:pid;   cid,A2:cid)
10	=A1.select(pid.sid.city.region==cid.city.region)
11	=A10.groups(cid.cid:cid;count(oid):count,sum(price*quantity):amount)

 

  Import the files respectively, create associations between the fact table and each of the dimension tables, get eligible orders according to the associations, and then groups them and performs aggregations. switch() function creates foreign key style associations.

  Compare files of different structures

  Data.txt is a tab-delimited, six-field text file, where here field contains semicolon-separated strings. List.txt is a single-column file. Task: If a record in Data.txt where the split here field value matches any line in List.txt, output this record to result.txt.

  List.txt:

Gee

Whiz

Lol

 

  Data.txt:

field1	field2	field3	here	field5	etc
A	B	2	Gee;Whiz;Hello	13	12
A	B	2	Gee;Whizz;Hi	56	32
E		4	Btm;Lol	16	2
T		3	Whizz	13	3

 

  SPL script:

	A
1	=file("d:\\Data.txt").import@t()
2	=file("d:\\List.txt").read@n()
3	=A1.select(here.array(";")^A2!=[])
4	=file("d:\\result.txt").export@t(A3)

 

  select() function gets desired records by splitting each here field value into a sequence of strings with array() function and calculating its intersection with A2’s sequence using operator “^” and according to the condition that the intersection result isn’t empty.

  The final result:

field1	field2	field3	here	field5	etc
A	B	2	Gee;Whiz;Hello	13	12
A	B	2	Gee;Whizz;Hi	56	32
E		4	Btm;Lol	16	2

 

  Data extraction from a multilevel directory

  D:\files is a multilevel directory where each subdirectory contains many text files. Task: Retrieve a specified line (say, the 2^nd one) from each file to write to d:\result.txt.

SPL script:

	A
1	=directory@p(path)
2	=A1.(file(~).cursor@s())
3	=A2.((~.skip(1),~.fetch@x(1)))
4	=A3.union()
5	=file("d:\\result.txt").export@a(A4)
6	=directory@dp(path)
7	=A6.(call("c:\\readfile.dfx",~))

 

  Set the initial value of parameter path as D:\files to begin data extraction; then call the script (c:\readfile.dfx) iteratively to pass different value to parameter path each time.

  directory() function gets files under the root directory specified by parameter path; @p option requires full paths and @d gets directory names only.

  ~.skip(1) skips the first line.

  ~.fetch@x(1) retrieves the line at the current position (the 2^nd line) and closes the cursor.

  Split a file as multiple ones

  sales.txt records a large number of orders. Task: Split the file into multiple files by year and month; the file name format is year-month.txt.

  SPL script:

	A
1	=file("D:\\   sales.txt").import@t()
2	=A1.group(string(OrderDate,"yyyy-MM");~)
3	=A2.run(file("d:\\temp\\"+#1+".txt").export(#2))

 

  Group the file by year and month and loop each group to write data to a target file. Below is file 2009-01.txt:

65  YZ  8  29600.0  2009-01-06 62  JAXE 11  8134.0  2009-01-06 64  HP  13    20000.0  2009-01-02 60  PWQ  16  3430.0  2009-01-05 63  SJCH  16  5880.0  2009-01-02 61  SJCH  19  1078.0  2009-01-08

 

  Use cursor to import the file if its size is greater than memory capacity. If a group is still can’t be imported into the memory wholly, use groupx() function to group the file. The code structure maintains unchanged.

  Stock calculation

  Stock.txt stores in-out stock records. One item may have multiple in-out records per day, or may have no records in days. The initial value for an item is 0. In means in-stock and out means out-stock. Task: Calculate stock for each item. Below is the source data:

date name  quantity  flag 2014-04-01  Item1  15  In 2014-04-01  Item1  4  In 2014-04-02  Item1  3  In 2014-04-02  Item1  10  Out 2014-04-03  Item1  3  In 2014-04-04  Item1  5  Out 2014-04-07  Item1  4  In 2014-04-10  Item1  2  Out 2014-04-01  Item2  20  In 2014-04-02  Item3  30  In 2014-04-03  Item3  14  Out

 

  SPL script:

	A	B
1	=file("D:\\stock.txt").import@t()
2	=A1.group(name,date;~.select(flag==”In”).sum(quantity):in,~.select(flag==”Out”).sum(quantity):out)
3	=A2.group(name)	=periods((t=A2.id(date)).min(),t.max(),1)
4	for A3	=A4.align(B3,date)
5		>c=0
6		=B4.new(A4.name:name,   B3(#):date,   c:Opening,   in,   (b=c+in):Total,   out,   (c=b-out):Close)
7		>A8=A8|B6
8

 

  A2 calculates the sum of quantities per item per day. B3 gets complete, continuous dates according to the earliest and the latest dates. Then group records by items and loop over each group to align records to B3. B6 calculates the each item’ daily stock and stores all stocks in A8.

  Below is A8’s result: