compare two files excel

[Mahdoucch03]

Hello

i have folder that contained two excel files .

first : 10-10-2021.xlsx  filed : ID

second : 11-10-2021.xlsx filed :ID

-I want to compare number of ID in the first file with the second file.

-I want to count the number of  ID in the second files where not exists in the first file.

-I want to count the number of ID in the first file and not exists in the second file

thank you.

[Vegar · Accepted solution]

You need somehow add information on which file a row comes from in your load script. You could use 

filebasename() as SourceFile when loading your excel file.

Consider my simplified example. Both "files" contains unique IDs except ID=3 that exists in both source files. 

Data:
LOAD *, '10-10-2021.xlsx' as SourceFile inline [
  ID
  1
  2
  3
];

LOAD *, '11-10-2021.xlsx' as SourceFile inline [
  ID
  3
  4
  5
  6
];

The expressions used to calculate your three questions are visible in the image below.