...

Combine tables with different columns horizontally

 

The join function is used to merge tables with different columns horizontally and perform the equivalence join.

Examples

Example 1: The data can be matched

There are three Excel files "emp.xlsx," which store the scores of math, PE, and English respectively. Part of the data is as follows:

..

..

..

Now the task is to combine the scores of the three subjects together horizontally, and the results are as follows:

..

Write the SPL script:

A

1

=["math","PE","english"].(file(~/".xlsx").xlsimport@t())

2

=join(A1(1):math,id;A1(2):PE,id;A1(3):english,id)

3

=A2.new(math.id,math.name,math.math,PE.PE,english.english)

4

=file("result.xls").xlsexport@t(A3)

A1 Read the student score data of math, PE, and English from Excel 

A2 Associate the data by id 

A3 Select the id, name, math, PE, and english columns

A4 Export results to result.xlsx

 

Example 2: The data can not be matched

There are three Excel files "emp.xlsx", which stores the scores of math, PE, and English respectively. However, the data in the files are not complete, where the math column lacks the score of Student 3 (id), PE column lacks the score of Student 2 (id), and english column lacks the score of Student 1 (id). Some of the data are as follows:

..

..

..

Now the task is to combine the scores of the three subjects together horizontally, and the results are as follows:

 ..

Write the SPL script:

A

1

=["math","PE","english"].(file(~/".xlsx").xlsimport@t())

2

=join@f(A1(1):math,id;A1(2):PE,id;A1(3):english,id)

3

=A2.new([math.id,PE.id,english.id].ifn():id,[math.name,PE.name,english.name].ifn():name,math.math,PE.PE,english.english)

4

=file("result.xls").xlsexport@t(A3)

A1: Read the student score of math, PE, and English from Excel.

A2: Associate the data by id, and the @f option is used for the full join. The association results are as follows:

..

If @f option is not used, data will be lost as follows:

..

A3: Select id, name, math, PE, and english columns. When selecting id and name, use the A.ifn function to make sure the data are actually selected because of the lack of values. 

A4: Export results to result.xlsx 

 

 


 
 •  261 View  •