topic Comparing data in QlikView

Comparing data

qw_johan — Sat, 05 Feb 2011 12:53:38 GMT

Hi,

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_Nr
+ ID_Name

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?

Thank you

AW:Comparing data

Sat, 05 Feb 2011 16:39:05 GMT

Hello Johan,

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:

QUALIFY *;

textdata:
Load * inline
[
ID, Name
1, EINS
2, ZWEI
3, DREI
7, SIEBEN
8, ACHT
];


exceldata:
Load * inline
[
ID, Name
1, Eins
2, Zwei
3, Drei
4, Vier
];


MissingInExcel:
LOAD
 *
Resident textdata
Where not Exists(exceldata.ID, textdata.ID);

MissingInText:
LOAD
 *
Resident exceldata
Where not Exists(textdata.ID, exceldata.ID);

Regards, Roland

AW:Comparing data

qw_johan — Sat, 05 Feb 2011 21:16:37 GMT

Thanks a lot for your help, Roland.

Re: AW:Comparing data

drjoffily — Thu, 08 Sep 2011 04:18:26 GMT

Hi Roland,

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:

Daily_Dump:

Load *From TradeDump.CSV

Yesterday_Daily_Dump:

Load *From TradeDump.qvd

MissingInDailyDump:

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

Any Idea?

Regards,

Marcos

Comparing data

drjoffily — Thu, 08 Sep 2011 06:51:22 GMT

Don't worry. I got it working now. It didn't like the "[" around Transaction Number. I changed the name of the column to Transaction_Number and it worked.

Cheers

AW:Comparing data

Tue, 13 Sep 2011 17:56:52 GMT

Hi qw_johan,

Marcos,

glad to help you.

Re: Comparing data

qlikviewforum — Tue, 15 Apr 2014 10:37:09 GMT

What we can do if we don't have unique fields in both the tables. How can we compare that? please help

Re: Comparing data

magavi_framsteg — Tue, 15 Apr 2014 11:42:28 GMT

What do you mean by "if we don't have unique fields in both the tables"?

Re: Comparing data

qlikviewforum — Tue, 15 Apr 2014 12:10:10 GMT

Means - "If you dont have the primary key"?

Re: Comparing data

Tue, 15 Apr 2014 12:16:51 GMT

Hi,

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.