concatenate 2 tables from 2 different databases

[ali_hijazi]

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?

[erichshiino]

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

Hope this helps,

Erich

[ali_hijazi]

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

[#4]

try

select *

from database1.dbo.TargetTable

concatenate

select * from database2.dbo.TargetTable

whitout Qualify.

[erichshiino · Accepted solution]

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

[ali_hijazi]

I need the qualify because multiple tables share same column names

I won't go into synthetic keys dilemma

[#7]

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;