topic Re: how to compare data between two qvd in QlikView

how to compare data between two qvd

Wed, 25 Nov 2020 16:16:04 GMT

how to compare data is same between 2 similar qvd files

Re: how to compare data between two qvd

psk180590 — Wed, 22 Feb 2017 08:38:00 GMT

Hi Pratheek,

What do you want to compare?

If you want to look at the data difference then you can do a Binary load into two separate qvw files and compare there.

Or might be this comparing data might help you .

Re: how to compare data between two qvd

Wed, 22 Feb 2017 08:42:32 GMT

Hi Sai

I have 2 qvd files with same fields and same data and want to compare all the data in the fields are matching.

So if let me know how to do the binary load

Re: how to compare data between two qvd

psk180590 — Wed, 22 Feb 2017 08:47:39 GMT

The script for Binary load is

Binary[YourQVD];

YourQVD is the name of the QVD file where you have the data.

Create a new qvw and write this statement this will fetch all the data from your QVD.

Re: how to compare data between two qvd

sarvesh — Wed, 22 Feb 2017 08:47:52 GMT

Hi..Sai,

Please refer below mentioned URL it may help you:

Compare 2 QVDs

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: how to compare data between two qvd

sarvesh — Wed, 22 Feb 2017 08:50:29 GMT

Hi..Pratheek,

My initial idea (some years ago when dealing with a problem with Incremental loads of very large tables) was:

Make a QVW document that:

Loads the first version of the QVD and adds a primary key to it
Loads the second version of the QVD and joins it to the first (OUTER JOIN)
Reloads the resulting table and creates flags for all field pairs that differ.
Create a few UI objects with counters for nr of records and a table with counters for field differences.

If you want to do it for all fields, you're in for a lot of work. If you want to do this for a few important fields, or just to count the number of differing records, it is doable.

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: how to compare data between two qvd

Wed, 22 Feb 2017 09:12:43 GMT

Hi Sarvesh

Could you please send the scripting for my refernce

Re: how to compare data between two qvd

sarvesh — Wed, 22 Feb 2017 09:24:02 GMT

Please check the attachment.

Re: how to compare data between two qvd

jonathandienst — Wed, 22 Feb 2017 09:31:02 GMT

That will work as long as the QVDs have a unique key field. If not, the join could cause problems. For this reason, I normally concatenate the two QVDs, like so:

Data:

LOAD *,

FileBaseName() as Source

From <first QVD>.qvd (qvd);

Concatenate(Data)

LOAD *,

FileBaseName() as Source

From <second QVD>.qvd (qvd);

Replace the placeholders with the actual filenames.

Re: how to compare data between two qvd

jonathandienst — Wed, 22 Feb 2017 09:35:56 GMT

Then use set expressions to select counts / sums / etc from file1 and file2. I usually use descriptive value for the Source field rather than the FileBaseName() as above.

So create a table with the relevant fields as dimensions, then use something like

File1 Customers:

Count({<Source = {'<first QVD name'}>} Customer)

File2 Customers:

Count({<Source = {'<seconds QVD name'}>} Customer)

Customer difference:

Count({<Source = {'<first QVD name'}>} Customer) - Count({<Source = {'<seconds QVD name'}>} Customer)

File1 Transactions:

Sum({<Source = {'<first QVD name'}>} Amount)

...

and so on.

Re: how to compare data between two qvd

ramasaisaksoft — Wed, 22 Feb 2017 12:40:25 GMT

use "qualify"

now 2 tables are different

now create the variable

Let vNoofrows1 = NoOfRows('TableName1')

Let vNoofrows2 = NoOfRows('TableName2')

these will give the no of rows from each table

if u want to compare field wise

count (distinct(t1.id) )

count (distinct(t2.id) )

just do like this and check by column wise, but it is a manual work