concatenate multiple odbc queries

[madmax88]

Hi all,

Is it possible to concatenate multiple SQL queries? E.g.

Query 1:

Load CONT_ID,

CONT_INV;

SQL SELECT CONT_ID,

CONT_INV

FROM abc.cont_file

Query 2:

Load CONT_ID,

ASSET_ID;

SQL SELECT CONT_ID,

ASSET_ID

FROM abc.pos_file

Query 3:

Load ASSET_ID,

ASSET_NAME;

SQL SELECT

ASSET_ID,

ASSET_NAME

FROM abc.asset_file

Target is a Table with

ASSET_ID,

CONT_INV,

ASSET_NAME

Reason:

I have a Table with

Table_1:

Name,

CONT_INV,

ASSET_ID,

Value

_________________________

CONT_INV and ASSET_ID and Value are dependent = The value is releated to both (CONT_INV and ASSET_ID)

Is it possible?

[dplr-rn]

Not 100% clear on the issue but Any sql query which is supported by your DB can be run

[fkskirer]

Hi Madmax,

Instead of straight concatenating the tables you could potentially use a Linktable instead. A key/link table is great for fixing synthetic keys or circular loops.  The goal is to create a data model with the dimension tables connected to a central key table (Linktable). But the Linktable doesn't contain the measures (in your case 'Value'), those generally stay in the dimension tables. 

Try something like this:

[Query 1]:
Load
AutoNumber(`CONT_ID`&'|'&`CONT_INV`) As [%Key_field],
CONT_ID,
CONT_INV;
SQL SELECT
CONT_ID,
CONT_INV
FROM abc.cont_file;

[Query 2]:
Load
AutoNumber(`CONT_ID`&'|'&`ASSET_ID`) As [%Key_field],
CONT_ID,
ASSET_ID;
SQL SELECT
CONT_ID,
ASSET_ID
FROM abc.pos_file;

[Query 3]:
Load
AutoNumber(`ASSET_ID`&'|'&`ASSET_NAME`) As [%Key_field],
ASSET_ID,
ASSET_NAME;
SQL SELECT
ASSET_ID,
ASSET_NAME
FROM abc.asset_file;

Table_1:
LOAD
AutoNumber(`Name`&'|'&`CONT_INV`&'|'&`ASSET_ID`) As [%Key_field],
Name,
CONT_INV,
ASSET_ID,
Value
From [YourLocation];

LinkTable:
Load
Distinct
[%Key_field],
[CONT_ID],
[CONT_INV]
Resident [Query 1];

Concatenate(LinkTable)
LOAD
Distinct
[%Key_field],
[CONT_ID],
[ASSET_ID]
Resident [Query 2];

Concatenate(LinkTable)
LOAD
Distinct
[%Key_field],
[ASSET_ID],
[ASSET_NAME]
Resident [Query 3];

Concatenate(LinkTable)
LOAD
Distinct
[%Key_field],
[Name],
[CONT_INV],
[ASSET_ID]
Resident [Table_1];

Drop Fields
[CONT_ID],
[CONT_INV]
From [Query 1];

Drop Fields
[CONT_ID],
[ASSET_ID]
From [Query 2];

Drop Fields
[ASSET_ID],
[ASSET_NAME]
From [Query 3];

Drop Fields
[Name],
[CONT_INV],
[ASSET_ID]
From [Table_1];

[krishna_2644]

Hi,

Qlik Automatically concatenates the tables with similar field names and same number of fields.

if you want to force a concatenation then do like below query.

Table:

load A, B from XYZ;

concatenate(Table)

Load B,C from PQR;

....

...