topic concatenate 2 tables from 2 different databases in QlikView

concatenate 2 tables from 2 different databases

ali_hijazi — Wed, 08 Jun 2011 10:29:29 GMT

Hello

I have 2 databases (residing on the same sql server) from which I want to select data from 2 tables

these 2 tables have the same name and structrue and contain about 35 columns

I want to concatenate both tables

I'm writing the following

Qualify *;

ChartofAccounts:

unqualify field1;

Load

field1,

field2,

...

select *

from database1.dbo.TargetTable

concatenate ChartofAccounts

load

field1,

field2,

...

sql select * from database2.dbo.TargetTable

the resulting table in qlikview is doubling the columns as follows:

i'm getting columns as follows:

field1

ChartofAccounts.field2,

ChartofAccounts-1.field2,

...

how can I solve this problem?

concatenate 2 tables from 2 different databases

erichshiino — Wed, 08 Jun 2011 10:34:25 GMT

I believe you should just remove the Qualify * in the beggining of your code

Hope this helps,

Erich

concatenate 2 tables from 2 different databases

ali_hijazi — Wed, 08 Jun 2011 10:36:04 GMT

I need the Qualify * because I will be encountering synthetic keys since this table contains multiple columns with the same name as columns of other tables

concatenate 2 tables from 2 different databases

Wed, 08 Jun 2011 10:36:13 GMT

try

select *

from database1.dbo.TargetTable

concatenate

select * from database2.dbo.TargetTable

whitout Qualify.

concatenate 2 tables from 2 different databases

erichshiino — Wed, 08 Jun 2011 10:40:59 GMT

I see...

Try this then:

tChartofAccounts:

Load

field1,

field2,

...

select *

from database1.dbo.TargetTable

concatenate tChartofAccounts

load

field1,

field2,

...

sql select * from database2.dbo.TargetTable

Qualify *;

unqualify field1;

noconcatenate

ChartofAccounts:

load * resident tChartofAccounts;

drop table tChartofAccounts;

(QV 10 syntax checker will not recognize noconcatenate as a valid command, but it works)

Regards,

Erich

concatenate 2 tables from 2 different databases

ali_hijazi — Wed, 08 Jun 2011 10:43:47 GMT

I need the qualify because multiple tables share same column names

I won't go into synthetic keys dilemma

concatenate 2 tables from 2 different databases

Tue, 01 Nov 2011 10:47:37 GMT

Another simpler approach:

Qualify *;

ChartofAccounts:

unqualify field1;

Load

field1,

field2,

...

select *

from database1.dbo.TargetTable

RENAME TABLE ChartofAccounts TO ChartofAccounts_ALL;

ChartofAccounts

load

field1,

field2,

...

sql select * from database2.dbo.TargetTable

RENAME TABLE ChartofAccounts TO ChartofAccounts_ALL;