Union vs Concat in QVD Gen

[mishraamit2485]

HI All,

We were using some view to generate qvd's, but now its performance is got degraded because some changes in the view itself.

Older Method:

ABC:

select * frm AMS_VIEW

Union

Select* frm APJ_VIEW

Union

Select * frm EMEA_VIEW;

Store ABC into ABC.QVD

New Method

ABC:

Load *

from AMS_VIEW

CONCAT

Load *

from APJ_VIEW

CONCAT

Load *

from EMEA_VIEW

Store ABC into ABC.QVD

Need to know is there any cases where concat will fail? I mean any corner cases.

[jonathandienst · Accepted solution]

Firstly  - the keyword is Concatenate. Concat is an aggregation function.

SQL unions require that the same fields, with the same data types exist in each SELECT in the same order.

QV concatenates do not require any of these, but 'missing' fields will get null values.

But I cannot think of a scenario where the QV concatenate would produce different results to an existing SQL union. But if SQL union works, why move it to QV?

[marcus_sommer]

I'm sure that you have no issue with concatenate. Your issues will be caused through the kind of your loads and from where you load the data. SELECT will be used to fetch data from databases per odbc/oledb-driver and LOAD is for loading external file-data or internal table-data. Please provide more (real) details from your load-script.

- Marcus

[mishraamit2485]

Thanks Jonathan.

Yes its Concatenate sorry for that.

[maxgro]

I think this is the new method (concatenate, not concat and SQL select, not load)

ABC:

SQL Select * from AMS_VIEW;

CONCATenate (ABC)

SQL select * from APJ_VIEW;

CONCATenate (ABC)

SQL * from EMEA_VIEW;

Store ABC into ABC.QVD;

concatenate (Qlik) doesn't remove duplicate

union (SQL) remove duplicate

so, if you have dup record in different views in your source db the result isn't the same

[mishraamit2485]

Hi Marcus.

Here are the details

Older:

ABC:

SQL

select A,B,c....Z

frm AMS_VIEW

Union

select A,B,c....Z

frm APJ_VIEW

Union

select A,B,c....Z

frm EMEA_VIEW;

Store ABC into ABC.QVD

New Method

ABC:

Load * ;

SQL

select A,B,c....Z

from AMS_VIEW;

Concatenate

Load * ;

select A,B,c....Z

from APJ_VIEW;

Concatenate

Load * ;

select A,B,c....Z

from EMEA_VIEW;

Store ABC into ABC.QVD

[mishraamit2485]

Hi Jonathan,

Actually we were creating our QVDs using union till now but suddenly the performance started coming down(Because we started getting more no records).

Someone suggested that use Concatenate instead of Union it will be faster and it is faster, but we were not sure if it will cause any data issue later on.

[marcus_sommer]

A preceeding load is often very helpful by loading data from databases but if you makes no adjustements or transformings to the database data you didn't need the preceeding. By operations which merge (concatenate/join/keep) tables you should use table-names to make sure that the operation will be applied to right table. Concret I mean:

...

concatenate (ABC)

...

- Marcus