QVD: Two DB`s same table structures

[Qlik_Enthu]

Dear all,

I am developing an app using QDF. My application involves tables of data from Two different databases, however the tables structures, table names and field names are similar. I need to create QVDs with the data with both the databases, concatenate the data to create KPIs and charts. Kindly suggest the ideal approach to do the same.

Thanks in advance!

Regards,
Saimeenakshi S

[Anonymous]

‌i would create an extraction and transformation layer Qvd generator. The extraction layer retrieves the data it is raw format. The transformation layer then applies business rules and ensures column names are consistent.

[kenphamvn]

Hi

You can merger two table as same struct from two data source into one QVD file by using Concatenate keyword

Script like

LIB CONNECT TO  Datasource1;

[QVDTable]:
load *;
SQL Select * from Datasource1.Tablename;


LIB CONNECT TO  Datasource2;

Concatenate

[QVDTable]:
load *;
SQL Select * from Datasource2.Tablename;

Store * from  QVDTable into 'QvdPath\QVDTable.QVD';

[Qlik_Enthu]

hi,

1. I have created qvds for both the db`s in same qvw application with (say QVD_DB2 and QVD_DB2)
2. placed it in the extract folder of my QVDPath (business rules or data filter is part of the sql query where clause hence no separate transform step added here)
3. stored it in Load folder of my QVDPath for presentation.
4. Then created a new QVW application and loaded the Load folder QVD`s by concatenate keyword. however the record count doesn't seems to match.

Purpose of me doing this way is to have separate count of records from both DB`s for creation of visualizations.

Kindly advice if I am doing anything not right here?

[Qlik_Enthu]

My piece of code is given below:

DB1:
//**********************CUSTOMER ORDER Table QVD Extract*******************************//
CUSTOMER_ORDER_EU:
SQL SELECT
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TRUNC(CUSORD_DATE)>=TRUNC(SYSDATE);
STORE CUSTOMER_ORDER_EU into $(vG.QVDPath)1.Extract\CUST_ORDER_EU.qvd(qvd);
Drop Table CUSTOMER_ORDER_EU;

//***********************CUSTOMER ORDER Table QVD LOAD*********************************//
CUSTOMER_ORDER_EU:
LOAD
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM [$(vG.QVDPath)1.Extract\CUST_ORDER_EU.qvd](qvd);
STORE CUSTOMER_ORDER_EU into $(vG.QVDPath)3.Load\CUST_ORDER_EU.qvd(qvd);
Drop Table CUSTOMER_ORDER_EU;

DB2:
//**********************CUSTOMER ORDER Table QVD Extract*******************************//
CUSTOMER_ORDER_EUX:
SQL SELECT
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TRUNC(CUSORD_DATE)>=TRUNC(SYSDATE);
STORE CUSTOMER_ORDER_EUX into $(vG.QVDPath)1.Extract\CUST_ORDER_EUX.qvd(qvd);
Drop Table CUSTOMER_ORDER_EUX;

//***********************CUSTOMER ORDER Table QVD LOAD*********************************//
CUSTOMER_ORDER_EUX:
LOAD
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM [$(vG.QVDPath)1.Extract\CUST_ORDER_EUX.qvd](qvd);
STORE CUSTOMER_ORDER_EUX into $(vG.QVDPath)3.Load\CUST_ORDER_EUX.qvd(qvd);
Drop Table CUSTOMER_ORDER_EUX;

QVD concatenate in end user qvw application:

CUSTOMER_ORDER_EU:
LOAD
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM [$(vG.QVDPath)3.Load\CUST_ORDER_EU.qvd](qvd);
Concatenate
CUSTOMER_ORDER_EUX:
LOAD
"BU_CODE",
"BU_TYPE",
"CUSORD_NO",
"CUSORD_DATE",
"CUSORD_STAT",
"CTY_CODE",
"USER_ID_REF",
"BU_CODE_SEND",
"BU_TYPE_SEND",
"UPD_DATE"
FROM [$(vG.QVDPath)3.Load\CUST_ORDER_EUX.qvd](qvd);