Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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];
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;
....
...