topic Re: Comparing two Datasources for differences in QlikView

Comparing two Datasources for differences

Wed, 24 Sep 2014 17:31:56 GMT

I have two data sources, one from Teradata and the other from Excel. All column names are aligned across these two data sets. I am loading both sources into my Dashboard and as expected QV is Joining them together.

If I was working in one database I could execute a Union All on these data sources and Group By every column with a Count to determine which records don't exist in both sources, i.e. count = 1, rather than 2. Wondering if there is a simple solution to this use case utilizing Qlikview?

Re: Comparing two Datasources for differences

Anonymous — Wed, 24 Sep 2014 17:40:43 GMT

you can use Qualify for the data loading.

And use Unqualify for the common dimension (attributes) to compare figures

Re: Comparing two Datasources for differences

maxgro — Wed, 24 Sep 2014 17:42:04 GMT

I think you can do the same thing (or get the same result) in Qlik,see attachment

Re: Comparing two Datasources for differences

mjayachandran — Wed, 24 Sep 2014 17:59:47 GMT

In ur load script for each table, have an extra column which can identify the table from which u r loading the data,

something like

TableA:

Load

'TableA' as Tablename,

From xyz

TableB:

Load

'TableB' as Tablename,

From xyz

Now in ur dashboard have all the columns in a pivot table with count of , you can easily identify in which table what data is missing

Re: Comparing two Datasources for differences

Wed, 24 Sep 2014 18:01:28 GMT

Hi Massimo, Thanks for the response; that looks like what I am trying to accomplish!. Unfortunatly I'm using the free desktop client and can't open your .qvw. We are still evaluting purchase of QV license. Can you please supply a screen shot of your object expression.

Many Thanks,

Lew

Re: Comparing two Datasources for differences

maxgro — Wed, 24 Sep 2014 18:21:44 GMT

this is the script; I did all in the script, trying to replicate your sql in database

the object is a table box, just fields, no dims or expressions

teradata:

load * inline [

a,b,c

1,2,3

4,5,6

7,8,9

a,b,c

];

excel:

NoConcatenate load * inline [

a,b,c

1,2,3

4,5,6

7,8,9

x,y,z

a,b,d

];

load *, 't' as type resident teradata;

Concatenate (t) load *, 'e' as type resident excel;

DROP table teradata, excel;

load

a,b,c, count(type) as cnt, concat(type) as type

Resident t

group by a,b,c;

DROP Table t;

Re: Comparing two Datasources for differences

Wed, 24 Sep 2014 18:25:00 GMT

Hi Mahesh, My load is just as you have described, just not sure what the expression would be that would group by all columns to get the count of duplicates.