topic Re: 2 Databases compare in QlikView

2 Databases compare

Tue, 03 Sep 2013 06:16:04 GMT

I have two Databases. They have both the same tables with the same name. I have to check if there are in both the same data. How should I do that?

Re: 2 Databases compare

Andrea_Ghirardello — Tue, 03 Sep 2013 07:07:23 GMT

You have several options:

A- you can create a table using inner join

B- you can load at first one table (master one) adding a new field (flag_1=1), then load the second one using different alias with an additional field (flag_2=2), finally you can create a table containing rows from both tables in this way:

1. Create new table FINAL_TABLE adding rows from table_1 inner join table_2 with a new field (flag=3)

2. add rows from table_1 where keys not exists in FINAL_TABLE (or using left join) and set flag=1

3. add rows from table_2 where keys not exists in FINAL_TABLE (or using right join) and set flag=2

4. drop tables table_1 and table_2

solution B is more complicated but you can identify each row: Rows with flag = 3 are rows that are common to Table_1 and Table_2; rows with flag = 1 are rows that are present only in Table_1; rows with flag = 2 are rows that are present only in Table_2

Re: 2 Databases compare

Tue, 03 Sep 2013 09:27:32 GMT

How can I add a new field?

Re: 2 Databases compare

Andrea_Ghirardello — Tue, 03 Sep 2013 09:34:04 GMT

For example:

LOAD

Field1,

Field2,

1 as AdditionalField

FROM .\YourData.qvd (qvd);

Re: Re: 2 Databases compare

Andrea_Ghirardello — Tue, 03 Sep 2013 10:12:47 GMT

See attached example

Re: 2 Databases compare

Thu, 05 Sep 2013 12:04:51 GMT

It's near to work but if i load it then i got an error: Table not found.

Isn't it possible to get a name to an ODBC Connection or do i have to take the name like in SQL: FROM a.table?

Re: 2 Databases compare

Andrea_Ghirardello — Thu, 05 Sep 2013 12:18:16 GMT

Do you mean that the example raise an error while reloading?

Did you estract source data .xlsx from the zip?

Re: 2 Databases compare

Thu, 05 Sep 2013 14:28:41 GMT

No yours working fine. I did one for my two ODBC Databases and there it didnt work

Re: 2 Databases compare

Andrea_Ghirardello — Thu, 05 Sep 2013 15:25:39 GMT

Post the script or a test environment.

Re: 2 Databases compare

Mon, 09 Sep 2013 05:48:49 GMT

ODBC CONNECT TO [DB01;DBQ=DB01] (XUserId is asdasdasd, XPassword is aPassword);

"DB01":

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

DATETIME

FROM Test1.table1;

ODBC CONNECT TO [DB02;DBQ=DB02] (XUserId is asdasdasdasdasssda, XPassword is anotherPassword);

"DB02":

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

DATETIME

FROM Test2."table2";

"InnerJoinTable":

Load *

Resident DB01;

Inner Join load *

Resident DB02;

"UnionTable":

Load *,

'matching rows' as Flag

Resident InnerJoinTable;

add Load *,

'DB01' as Flag

Resident DB01;

add LOAD *,

'DB02' as Flag

Resident DB02;

drop Table InnerJoinTable;

Re: 2 Databases compare

tresB — Mon, 09 Sep 2013 06:01:33 GMT

It seems that you might be getting the error for table DB02, because this table is getting concatenated to DB01 (because all fields are common for them). You have to avoid this auto concatenation by using NOCONCATENATE keyword like:

ODBC CONNECT TO [DB01;DBQ=DB01] (XUserId is asdasdasd, XPassword is aPassword);

"DB01":

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

DATETIME

FROM Test1.table1;

ODBC CONNECT TO [DB02;DBQ=DB02] (XUserId is asdasdasdasdasssda, XPassword is anotherPassword);

"DB02":

NoConcatenate

Load *,

Date(Floor(DATETIME)) as Date Where

Date(DATETIME,'DD.MM.YYYY')>='23.07.2013';

SQL SELECT ID,

DATETIME

FROM Test2."table2";

Re: 2 Databases compare

Mon, 09 Sep 2013 06:25:58 GMT

if I load it then there is:

Connecting to DB01;DBQ=DB01

Connected

DB01 << table1 x lines fetched

Connection to DB02;DBQ=DB02

Conncetec

DB01<< table2

....

Is that normal that DB01 is two times there.

Could also the tablenames be a problem, because they have the same name, just from another DB

Re: 2 Databases compare

Mon, 09 Sep 2013 06:46:00 GMT

I'm getting the error: DB02 not found

Re: 2 Databases compare

tresB — Mon, 09 Sep 2013 06:57:01 GMT

Have you put NoConcatenate properly?

Re: 2 Databases compare

Mon, 09 Sep 2013 06:58:23 GMT

Wait I'm not sure. I look shortly

Re: 2 Databases compare

Mon, 09 Sep 2013 07:03:39 GMT

My fault. I put it in front of InnerJoinTable....
It's loading now and looks better. I'll write when it's finished with loading

Re: 2 Databases compare

Andrea_Ghirardello — Mon, 09 Sep 2013 07:06:50 GMT

You should use aliases for fields of each table as I showed in the last attached example because without aliases, you add rows to the same table.