RaqForum 22 No.
1 Reply  •  557 View  • 
     ...

Calculate the Intersection, Union, and Difference of Row-Style Data

 

Example

Example 1: Calculate intersection, union and difference for key columns

There is an Excel file Book1.xlsx, and the data is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey
4 Kayla Hailey Jennifer Austin Matthew

There is an Excel file Book2.xlsx, and the data is as follows:

team member1 member2 member3 member4 member5
10 Daniel Kayla Sarah Luis Ryan
15 Abigail Hailey Antony Madison Timothy
11 Haley Stephanie Justin Joseph Bryan
13 Timothy Joseph Hailey Katherine Lauren
12 Ryan David Isabella Julia Jose
9 Julia Haley Elizabeth Julia Alyssa
14 Jose Christian Claire Dylan Jose
8 Christian Lauren Justin Austin Chloe

Calculate the intersection, union, and difference between two sets of data according to the team column.

 

Write SPL script:


 A
1 =file("Book1.xlsx").xlsimport@t()
2 =file("Book2.xlsx").xlsimport@t()
3 =[A1,A2].merge@ou(team)
4 =[A1,A2].merge@oi(team)
5 =[A1,A2].merge@od(team)

A1 Read the Excel data

A2 Read the Excel data

A3 Union them by team column, and the result is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey
4 Kayla Hailey Jennifer Austin Matthew
15 Abigail Hailey Antony Madison Timothy
11 Haley Stephanie Justin Joseph Bryan
13 Timothy Joseph Hailey Katherine Lauren
12 Ryan David Isabella Julia Jose
14 Jose Christian Claire Dylan Jose

A3 Intersect them by team column, and the result is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey

A5 Calculate the difference by team column, and the result is as follows:

team member1 member2 member3 member4 member5
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
4 Kayla Hailey Jennifer Austin Matthew

 

Example 2: Calculate the intersection, union and difference of the entire row

There is an Excel file Book1.xlsx, and the data is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey
4 Kayla Hailey Jennifer Austin Matthew

There is an Excel file Book2.xlsx, and the data is as follows:

team member1 member2 member3 member4 member5
10 Daniel Kayla Sarah Luis Ryan
15 Abigail Hailey Antony Madison Timothy
11 Haley Stephanie Justin Joseph Bryan
13 Timothy Joseph Hailey Katherine Lauren
12 Ryan David Isabella Julia Jose
9 Julia Haley Elizabeth Julia Alyssa
14 Jose Christian Claire Dylan Jose
8 Christian Lauren Justin Austin Chloe

Calculate the intersection, union, and difference between two sets of data based on the entire row.

 

Write SPL script:


 A
1 =file("Book1.xlsx").xlsimport@t()
2 =file("Book2.xlsx").xlsimport@t()
3 =[A1,A2].merge@ou()
4 =[A1,A2].merge@oi()
5 =[A1,A2].merge@od()

A1 Read the Excel data

A2 Read the Excel data

A3 Union them by the entire row, and the result is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey
4 Kayla Hailey Jennifer Austin Matthew
10 Daniel Kayla Sarah Luis Ryan
15 Abigail Hailey Antony Madison Timothy
11 Haley Stephanie Justin Joseph Bryan
13 Timothy Joseph Hailey Katherine Lauren
12 Ryan David Isabella Julia Jose
9 Julia Haley Elizabeth Julia Alyssa
14 Jose Christian Claire Dylan Jose
8 Christian Lauren Justin Austin Chloe

A4 Intersect them by the entire row, and the target result does not exist because there is no same record in the two sets of data.

A5 Calculate the difference set by the entire row, and the result is as follows:

team member1 member2 member3 member4 member5
9 Taylor Stephanie Stephanie David Amanda
7 Ian Angela Brooke Steven Timothy
5 Nicholas Michael Madeline Antony Logan
3 Sophia Jacob Andrew Alexis Brianna
6 Christopher Alyssa Benjamin Thomas Courtney
1 Hannah Isabella Abigail Logan Mary
2 Benjamin Stephanie Jennifer Jacob Jose
10 Katherine Jonathan Kayla Isabella Luis
8 Daniel Justin Dylan Amanda Hailey
4 Kayla Hailey Jennifer Austin Matthew


 
 •  557 View  •