Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

qw_johan
Contributor

Comparing data

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

9 Replies
Not applicable

AW:Comparing data

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

qw_johan
Contributor

AW:Comparing data

Thanks a lot for your help, Roland.

drjoffily
New Contributor III

Re: AW:Comparing data

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

drjoffily
New Contributor III

Comparing data

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

Not applicable

AW:Comparing data

Hi qw_johan,

Marcos,

glad to help you.

RR

qlikviewforum
Contributor II

Re: Comparing data

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

magavi_framsteg
Contributor III

Re: Comparing data

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

qlikviewforum
Contributor II

Re: Comparing data

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

Not applicable

Re: Comparing data

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.

RR