Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Enthu
Creator II
Creator II

QVD: Two DB`s same table structures

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

4 Replies
Anonymous
Not applicable

‌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
Creator III
Creator III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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);