I need to compare two tables from different sources (textfile, excel file) to see if they match.
If they don't match I'd like to show the difference in another listbox or with highlighting.... or someting else.
I have a textfile(file1) with data, and an excelfile(file2) with data.
Most fields are named the same, at least the importent fields that I need to compare.
I want to make sure that the following fields has the same data in them in both files.
ID_Name is a string.
ID_Nr is integer (sometimes starting with one or two '0' zeros)
I need to be able to compare them individually (and display them individually) as well as together.
I mean compare ID_Nr in both files, display difference and compare ID_Name in both files, display difference.
What is the best way to do this?
there are more than one way. I would prefer using a "not exists". For performance reasons I would do a qualified load from both external resources (if you like, delete them afterwards). Next step is a resident load to get the missing rows. Please take a look at the following code snippet:
Load * inline
Load * inline
Where not Exists(exceldata.ID, textdata.ID);
Where not Exists(textdata.ID, exceldata.ID);
Just in relation to your reply to Johan... I'm trying to use the solution you mentioned above where you're using RESIDENT with NOT EXIST. I like the idea but I can't make it work.
What I'm trying to achieve is, get the difference between to datasets. They have identical structure (same columns). I want to compare both and get the difference between them. What is in one table and not in the other.
Here is what I'm trying to do:
Load *From TradeDump.CSV
Load *From TradeDump.qvd
LOAD * Resident Yesterday_Daily_Dump
Where not Exists(MissingInDailyDump.[Transaction Number],Daily_Dump.[Transaction Number]);
store MissingInDailyDump into MissingInDailyDump.qvd(qvd);
This give me syntax error because it doesn't like "MissingInDailyDump.[Transaction Number]"
If I remove "MissingInDailyDump.", when I run it it throws an error saying it doesn't know Transaction Number
the "not exists" is independend of the existance of a primary key field. It works also with non-unique fields.
Nevertheless, are you able to post some example data or an exam app? This would help the guys here to find a solution.