Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.