...

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


 
 •  334 View  •