Here’s how to use news() function.
Description:
Compute field values based on a table sequence/record sequence, and concatenate them to generate a new one.
Syntax:
A.news(X;xi:Fi,…)
Note:
The function computes field values based on record sequence X, concatenates the newly-computed fields to create a new table sequence/record sequence where parameter xi is field value of each record and Fi is the field name, which will be automatically identified according to parameter xi if Fi is omitted. When parameter xi is #i, it represents the ith field and the original field name will be used.
Parameter:
A |
A table sequence/record sequence |
X |
A record sequence/integer; equivalent to to(X) if it is an integer, meaning the frequency of inserting each record in A |
xi |
Expression, whose results will be field values; the sign ~ used in the parameter references data from record sequence X instead of A. The sign # is used to represent a field with a sequence number |
Fi |
Field name of the new table sequence/record sequence; when omitted, it will be automatically identified |
Option:
@1 |
Left join, which creates an empty record when record sequence X is empty |
Return value:
A new table sequence/record sequecne
Example:
|
A |
|
1 |
=demo.query("select * from EMPLOYEE") |
|
2 |
=A1.group(GENDER;~:group,~.avg(SALARY):avg) |
Perform group by GENDER |
3 |
=A2.news(group;EID,NAME,GENDER, #3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,A2.avg:AvgSalary) |
A2 is composed of data groups; based on each group field values are computed and a table sequence is generated; and then table sequences are concatenated into a new one. |
4 |
=A1.news(2;EID,NAME,GENDER,STATE,age(A1.BIRTHDAY):AGE) |
Generate a new table sequence by inserting each record twice |
5 |
=A1.group(DEPT).(~.group(GENDER)) |
Group records first by DEPT then by GENDER |
6 |
=A5.news(~;A5.~.DEPT:DEPT,A5.~.~.GENDER:GENDER,A5.~.~.avg(SALARY):AvgSalary) |
Get DEPT values and GENDER values from A5 to show the average salaries of different GENDERs in every DEPT |
7 |
=MySQL1.query("select * from EMPLOYEE") |
|
8 |
=A7.group(GENDER;~:group) |
Group GENDER field of EMPLOYEE table in MySQL1 database |
9 |
=A1.group(GENDER;avg(SALARY):avg) |
Group GENDER field of EMPLOYEE table in demo database |
10 |
=A9.join(GENDER,A8:GENDER,group) |
|
11 |
=A10.news@1(group;EID,NAME,GENDER,age(~.BIRTHDAY):age,salary,avg:AvgSalary) |
Compute each GENDER value for each group, generate an empty table for the group where GENDER is M and then concatenate result records into a new table sequence |
Related function:
Description:
Get new values for the fields of records in a channel and update them into the channel.
Syntax:
ch.news(X;xi:Fi,…)
Note:
With channel ch, the function computes values of fields in record sequence X and updates the new field values into the channel. Fi is the new field name that will be automatically identified if the parameter is omitted; xi represents the new field values. This is an attached computation.
Parameter:
ch |
Channel |
X |
Record sequence |
xi |
Expression, whose results will be field values; the sign ~ used in the parameter references data from X instead of A. The sign # is used to represent a field with a sequence number |
Fi |
Field name in the given channel; will be automatically identified if the parameter is omitted |
Return value:
The original channel with new fields
Example:
|
A |
|
1 |
=demo.cursor("select * from EMPLOYEE") |
|
2 |
=demo.query("select * from EMPLOYEE") |
|
3 |
=channel() |
Create a channel |
4 |
=A2.group(GENDER;~:group) |
Group records by GENDER |
5 |
=A1.groupx(GENDER;avg(SALARY):avg) |
Group records by GENDER |
6 |
=A5.join(GENDER,A4:GENDER,group) |
|
7 |
=A3.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary) |
A6 is one of the groups; compute field values based on each group and concatenate the results into A3’s channel |
8 |
=A3.fetch() |
Fetch and store the existing data in the channel |
9 |
=A6.push(A3) |
Be ready to push data in A6’s cursor into A3’s channel, but the action needs to wait |
10 |
=A6.fetch() |
Data in A6’s cursor is pushed into the channel and operations are performed as the fetch() operation is performed over A6 |
11 |
=A3.result() |
|
Description:
Compute new cursor field values to update them into the original cursor.
Syntax:
cs.news(X;xi:Fi,…)
Note:
The function computes field values based on cursor cs, updates the newly-computed fields into the original cursor where xi is field value of each record and Fi is the new field name, which will be automatically identified if Fi is omitted.
Parameter:
cs |
Cursor |
X |
Record sequence |
xi |
An expression, whose values are uses as the new field values. It is treated as null if omitted; in that case, : Fi can’t be omitted. The sign # is used to represent a field with a sequence number |
Fi |
Filed name of the new cs; use the identifiers parsed from expression xi if it is omitted |
Option:
@1 Left join, which creates an empty record when record sequence X is empty
Return value:
The original cursor with the new field values
Example:
|
A |
|
1 |
=demo.cursor("select * from EMPLOYEE") |
|
2 |
=demo.query("select * from EMPLOYEE") |
|
3 |
=A2.group(GENDER;~:group) |
Perform group by GENDER |
4 |
=A1.groupx(GENDER;avg(SALARY):avg) |
Perform group by GENDER |
5 |
=A4.join(GENDER,A3:GENDER,group) |
|
6 |
=A5.news(group;EID,NAME,GENDER,#3:surname, age(~.BIRTHDAY):age,SALARY+50: salary,avg:AvgSalary) |
A4 is composed of data groups; based on each group field values are computed and a cursor is generated; and then cursors are concatenated into a new one. |
7 |
=A6.fetch() |
|
8 |
=MySQL1.query("select * from EMPLOYEE") |
|
9 |
=A8.group(GENDER;~:group) |
Group GENDER field |
10 |
=A4.join(GENDER,A9:GENDER,group) |
|
11 |
=A10.news@1(group;EID,NAME,GENDER,age(~.BIRTHDAY):age,salary,avg:AvgSalary) |
Compute each GENDER value for each group, generate an empty table for the group where GENDER is M and then concatenate result records into a new table sequence |
12 |
=A11.fetch() |
|
Related functions:
A.news(X;xi:Fi,…)
Description:
Get values of specified field(s) in a composite table according to key values in a table sequence or a cursor, concatenate them and return result as a table sequence or a cursor.
Syntax:
T.news(A/cs:K,x:C,…;wi,...)
Note:
Composite table T and table sequence A or cursor cs have a relationship of many-to-one. The function retrieves specified field values x,... from T and concatenate them to form a new table sequence according to the correspondence between table sequence A or cursor cs’s key/dimension values and T’s fields (begin form the first field). It is required that A/cs be ordered by the key and their key has same order as that of T’s first field keys.
It is used to join a table sequence or a cursor to a composite in a one-to-many relationship and return a result set containing all records in the composite table pointing to the table sequence or cursor’s primary key. While T.new() function performs a join in a one-to-one or one-to-many relationship.
Parameter:
T |
A composite table |
A/cs |
A table sequence/cursor/composite table cursor |
K |
Field names; when there are K… parameters after A/cs, correspond them with theses specified fields |
x |
Field values |
C |
Column alias |
wi |
Filtering condition; multiple conditions are separated by comma(s) and they must be all met at the same time |
Option:
@r Copy records of the primary table and return result that aligned by A/cs and where aggregation can be performed
Return value:
A table sequence/cursor
Example:
|
A |
|
1 |
=file("score.ctx").open() |
Open a composite table file
|
2 |
=demo.query("select * from students").keys(ID) |
Return a table sequence with ID as the key |
3 |
=A1.news(A2:ID,STUDENTID,SUBJECT,SCORE;SUBJECT=="Math",SCORE>70) |
Join A1’s composite table and A2’s table sequence where the filteing condition is SUBJECT=Math & SCORE>70 and return eligible records in the composite table |
4 |
=A1.news@r(A2:ID,NAME,sum(SCORE):TotalScore) |
Sum scores of all subjects for each student and return result: |
Description:
Get values of specified field(s) from a cluster table according to key values of a cluster in-memory table or a cluster cursor, and concatenate them into a new table sequence or a cluster multicursor.
Syntax:
T.news(A/cs,x:C,…;wi,...)
Note:
The function retrieves values of field(s) x,... from composite table T according to key values in cluster in-memory table A or cluster cursor cs, and concatenates them to form a new table sequence or a cluster multicursor. A and cs should be ordered by the key, and their key should keep the same order as the existing key field(s) in T.
Parameter:
T |
A cluster table |
A/cs |
A cluster in-memory table/a cluster cursor |
x |
A field value |
C |
Column alias |
wi |
Filtering condition; multiple conditions are separated with comma and their relationships are AND |
Option:
@z |
Match existing fields of A/cs with T’s dimension/key |
Return value:
A table sequence or a cluster multicursor
Example:
|
A |
|
1 |
=file("scores.ctx",["192.168.31.165:8281"]).open() |
Return a cluster table |
2 |
=file("student.ctx",["192.168.31.165:8281"]).open().memory() |
Return a cluster in-memory table |
3 |
=A1.news(A2,STUDENTID,SUBJECT,SCORE;SUBJECT=="Math",SCORE>70) |
Return a table sequence |