A .derive()

Description:

Add one or more fields to a table sequence or a record sequence.

Syntax:

A.derive(xi:Fi,…)

Note:

The function adds Fi,… field(s) to table sequence/record sequence A to re-structure it as a table sequence with "original fields in P plus Fi,…", and then traverses records of A to assign each Fi with value xi.

Parameters:

Fi

Field name, which shouldn’t have the same name as any of the original fields in P

xi

Expression, whose results are used as the values of the derived fields

A

A table sequence/record sequence

Options:

@m

Use parallel algorithm to handle data-intensive or computation-intensive tasks to enhance performance; no definite order for the records in the result set.

@i

Won’t generate a record if the result of expression xi is null (this won’t affect a record with null value in the original table sequence)

@x(…;n)

Unfold original fields whose values are records into n levels; default of n is 2

Return value:

A table sequence with added field(s)

Example:

 

A

 

1

=demo.query("select NAME,BIRTHDAY,HIREDATE from EMPLOYEE")

2

=A1.derive(interval@y(BIRTHDAY, HIREDATE):EntryAge,

age(HIREDATE):WorkAge)

3

=A1.derive@m(age(HIREDATE):WorkAge)

Use the @m option to increase performance of big data handling.

4

=file("D:\\txt_files\\data1.txt").import@t()

Below is the file data1.txt:

5

=A4.derive@i(SCORE:score_not_null)

If the SCORE value is null, the corresponding record won’t be generated

6

=demo.query("select * from DEPARTMENT")

7

=demo.query("select NAME,GENDER,DEPT,SALARY from EMPLOYEE")

 

8

>A7.switch(DEPT,A6:DEPT)

Switch values of DEPT of A7’s table over with corresponding records

9

=A7.derive(SALARY*5:BONUS)

Add a new field:BONUS

10

=A7.derive@x(SALARY*5:BONUS)

Use @x option to unfold the DEPT field whose values are records; the default unfolding levels are 2

Note:

The difference between new() and derive(): The new() constructs a new table sequence without changing the original one. By comparison, the derive() copies the original fields and then adds new fields.