Need to compare 2 KV files and report the missing records of File1 in File2
File 1:
Row# | roll numbers | Name | Registration # |
1 | 5 | ||
2 | 7 | Ajay | 999 |
3 | 13 | Kishore | 123 |
4 | 10 | ||
5 | Vijay |
File2:
Row# | Class | roll numbers | Section | Name | Registration # |
1 | V | 2 | A | Aaron | 565 |
2 | VI | 4 | B | Michel | 321 |
3 | IV | 3 | D | Jeff | 678 |
4 | VIII | 7 | E | Ajay | 999 |
5 | X | 8 | H | Kumar | 767 |
6 | XII | 10 | F | 098 | |
7 | XI | 12 | N | Evan | 345 |
now, I want to compare the following columns:
Roll numbers(File1) against Roll numbers(File2)
Name(File1) against the Name(File2)
Registration #(File1) against the Registration#(File2)
if we find any one of the record(Roll Numbers/ Name/ Registration #) of File1 in File2 then we will not report that, I shall only report the record which we couldn't find anything by either roll number/name/registration#
Note - We have to compare FILE1 against FILE 2 and report the FILE1 missing records in File2
Desired OUTPUT
Row # | roll numbers | Name | Registration # |
1 | 5 | ||
2 | 13 | Kishore | 123 |
3 | Vijay |
Total 3 records not found (present in File1 and not present in File2)
How about something like this? I took your data and saved it into csv lookups called file1.csv and file2.csv. Also, based on your example data I believe you should have four results returned because the line with "roll numbers" set to 10 does not have a matching "Registration #".
| inputlookup file1.csv
| lookup file2.csv "roll numbers" as "roll numbers", Name as Name, "Registration #" as "Registration #" OUTPUT "Row#" as row_num
| search NOT row_num=*
| streamstats count as "Row#"
| table Row# "roll numbers" Name "Registration #"
Did this get what you want? If so, please accept the solution. If not, let me know and I'll try and fix whatever isn't right in it. Thanks.