concatenation of tables not happening(leading to failed execution)

[aayush__23]

Hi,

so I am combining 3 tables (concatenating them basically) and when reloading, The execution window either shows stuck at the lines or it fails the execution. 

Here is the script :

combined_table:

LOAD *
FROM [D:\QV\HRH_email.qvd]
(qvd);

Concatenate (combined_table)

LOAD *
FROM
[D:\QV\parallel31_email.qvd]
(qvd);

Concatenate (combined_table)

LOAD *
FROM
[D:\QV\Inhouse_email.qvd]
(qvd);

STORE combined_table INTO combined_table.QVD;
DROP TABLE combined_table;

(p.s: tried removing store and drop statements but still the same error which is no error showing up except failed execution)

[marcus_sommer]

Which kind of error?

[aayush__23]

Sir that's the issue. No error. Just execution window closing resulting in the file closing and then followed by the msg "Execution of script failed. Reload old data?"

[marcus_sommer]

Comment the complete concatenate-loads and the store + drop statement and if it runs look within the data-model if there is a table "combined_table".

[aayush__23]

I did what you suggested. No table named combined_table found in the data model.

Any idea what might be causing this issue??

[marcus_sommer]

A concatenation could be forced with a statement like yours of:

Concatenate (combined_table)

but even without this explicit statement tables are automatically concatenated - if they have the same data-structure. This means your first load-statement from here:

combined_table: LOAD * FROM [D:\QV\HRH_email.qvd] (qvd);

doesn't creates this new table else is added to a previous loaded table and therefore your following statement to concatenate/store/drop are applied to a non exists table and cause therefore the error.

To avoid such trouble you could apply a noconcatenate like:

combined_table: noconcatenate LOAD * FROM [D:\QV\HRH_email.qvd] (qvd);

[aayush__23]

Hi, sorry to bother again but when after using noconcatenate, synthetic keys are forming in each of the 3 tables and now I am left to wonder if there is any alternative to my concatenating problem because as long as syn keys exist, tables wont automatically combine

[marcus_sommer]

If this previous table is important you could concatenate those 3 qvd-loads to this table and otherwise just drop this table.

[Luis_Galvan]

In the past, I had issues when the first table loaded did not have values; therefore, no table is created in the data-model. A turn around is create a previous table using inline statement. That will, definitely, create a new table in the data-model, and next loaded tables will be concatenated naturally (when the tables shares same structure) or by force using concatenate

Once the concatenation ends, you can drop that extra field created in the inline table!

NoConcatenate
combined_table:
LOAD * inline [
dummyfield
1
];


Concatenate (combined_table)
LOAD *
FROM [D:\QV\HRH_email.qvd]
(qvd);

Concatenate (combined_table)
LOAD *
FROM
[D:\QV\parallel31_email.qvd]
(qvd);

Concatenate (combined_table)
LOAD *
FROM
[D:\QV\Inhouse_email.qvd]
(qvd);

DROP Field dummyfield;

STORE combined_table INTO combined_table.QVD;
DROP TABLE combined_table;

Hope this solves the problem you have

Cheers!

Luis G

[marcus_sommer]

An empty table like:

dummy: load 'dummy' as dummy autogenerate 0;

is not a problem to concatenate other tables to it and there are scenarios in which such an approach is quite practically. Whereby applied in this simple way it would prevent the concatening qvd-loads to load optimized (of course this could be adjusted by not using an extra dummy-field else to list all fields from the qvd).

Better is mostly to avoid such a workarounds and designing the workflow of loads within an application as well as between multiple application respectively different layers more systematically.