topic Compare two tables and see only differences - SQL version in QlikView

Compare two tables and see only differences - SQL version

Wed, 13 Nov 2013 20:25:08 GMT

Hi all,

I'm very new to QlikView.

I'm trying to compare two tables that should contain the same data, and I found this question, among others.

The solution offered works beautifully -- with an inline table.

If I redo the solution with SQL queries (both of which must contain joins) and run in debug mode, I get the error:

Unknown statement

INNER JOIN (Table)

My script looks something like:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

OLEDB CONNECT TO connection_string;

LOAD [columns], 'Table 1' as Table;

SQL SELECT *

FROM some_table

inner join [statements];

CONCATENATE

OLEDB CONNECT TO different_connection_string;

LOAD [columns], 'Table 2' as Table;

SQL SELECT *

FROM another_table

INNER JOIN [statements];

INNER JOIN (Table)

Re: Compare two tables and see only differences - SQL version

Clever_Anjos — Wed, 13 Nov 2013 20:59:41 GMT

You´re mixing QlikView and SQL sources

You should do

1) Retrieve the data from Source1 (Table1)

2) Retrieve the data from Source2 (Table2) you can´t do a join between them here

LOAD * resident Table1

inner join

LOAD * resident Table2

Re: Compare two tables and see only differences - SQL version

Anonymous — Wed, 13 Nov 2013 23:22:42 GMT

I think this syntax is correct however have not tested it.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO connection_string;

Table1:

LOAD [columns], 'Table 1' as Table;

SQL SELECT *

FROM some_table;

OLEDB CONNECT TO different_connection_string;

Table2:

LOAD [columns], 'Table 2' as Table;

SQL SELECT *

FROM another_table;

NoConcatenate

Join_Table:

Load *

Resident Table1;

Inner Join(Table1)

Load *

Resident Table2;

DROP Table Table1;

Drop Table Table2;

Re: Compare two tables and see only differences - SQL version

Wed, 20 Nov 2013 18:57:58 GMT

Here's a template that works, for anyone else who's wondering. One of the QlikView developers here helped me out - I'm still at the point where I need very detailed help with syntax.

Thanks to all who replied.

The connection strings need to precede the LOAD statements for the two tables.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO first_connection_string;

Table:

LOAD columns, 'Table_1' as Table;

SQL SELECT sql';

OLEDB CONNECT TO second_connection_string;

CONCATENATE (Table)

LOAD columns, 'Table_2' as Table;

SQL SELECT sql;

INNER JOIN (Table)

LOAD *

WHERE "Only in One Table?"

;

LOAD

key

,count(key)<2 as "Only in One Table?"

RESIDENT Table

GROUP BY key

;