topic Re: Issue with Loading data from two databases in QlikView

Issue with Loading data from two databases

Qlik_Enthu — Mon, 19 Jun 2017 11:30:58 GMT

Hi all,

I am trying to load data from two different databases with same table structure and column names.

My script as follows:

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
EU:
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
EUX:
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

However, I could see that after reload only the first table got loaded. I have no idea why the second table is not loading. Any thoughts ?

Re: Issue with Loading data from two databases

antoniotiman — Mon, 19 Jun 2017 11:38:59 GMT

Hi,

because You have same Name Fields in both table, then QV Concatenate

Re: Issue with Loading data from two databases

sunny_talwar — Mon, 19 Jun 2017 11:39:09 GMT

Try this

ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
EUX:

NoConcatenate
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

Re: Issue with Loading data from two databases

RonaldDoes — Mon, 19 Jun 2017 11:52:28 GMT

stalwar1‌ is right, you could use NoConcatenate

However, there are good reasons for concatenating these two tables. Without your full requirements it's hard to say which solutions works best from an architectural point of view.

The other option is adding a " Source"-field to your table.

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
TableName:
LOAD
'EU' AS Source,
*;
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
Concatenate (TableName):
LOAD
'EUX' AS Source,
*;
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

Re: Issue with Loading data from two databases

antoniotiman — Mon, 19 Jun 2017 12:07:42 GMT

I don't recommend You to use NoConcatenate, because after reload You will have many synthetic Keys.

Without ,You have Table 'Union' of Two tables. Why this is problem ?

Re: Issue with Loading data from two databases

antoniotiman — Mon, 19 Jun 2017 12:19:39 GMT

Another way to have two Distinct Tables is Qualify instruction.

e.g.

Table:

LOAD (or Select) A,B,C,D from File1;

Qualify *;

Table1:

LOAD (or Select) A,B,C,D from File2;

UnQualify *;

Table1 will have Names Field like Table1.A,Table2.B,......

Re: Issue with Loading data from two databases

datanibbler — Mon, 19 Jun 2017 12:50:12 GMT

Hi,

you could also load the first table, then write it to a qvd, delete it, then load the second table - and then load the first one again from the qvd (much faster this time) with a >> concatenate << - if you do want to concatenate the tables.

Otherwise, using the QUALIFY statement as Antonio suggests would work well, making all the field_names different between the tables.

Any way, it's hard to tell you how to do what you want to do without knowing what it is that you want to do 😉

Best regards,

DataNibbler

Re: Issue with Loading data from two databases

Qlik_Enthu — Mon, 19 Jun 2017 13:32:36 GMT

Dear All, my requirement is loading both the table (who has exactly same structure) from two diff. DB`s. Need to create a Dashboard with count () orders from the tables (separately and together) that's one of the KPIs goes into the Dashboard. similarly I need to create other KPI`s together and separately. How can I achieve this?

Concatenate? Join? or Union?

As mentioned by Antonio NoConcatenate explicitly loads however I get synthetics.

Re: Issue with Loading data from two databases

RonaldDoes — Mon, 19 Jun 2017 13:37:22 GMT

Hi Saimeenakshi,

I would strongly suggest concatenating and adding a source-field for this.

That seems to suit your requirement perfectly to me.

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
TableName:
LOAD
'EU' AS Source,
*;
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');
ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
Concatenate (TableName):
LOAD
'EUX' AS Source,
*;
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

Re: Issue with Loading data from two databases

sunny_talwar — Mon, 19 Jun 2017 13:38:13 GMT

From what you just mentioned, I don't think you need to worry about anything.... the tables are getting Auto-Concatenated and all the data resides in your table number 1 (data from table number 2 also).

Re: Issue with Loading data from two databases

avinashelite — Mon, 19 Jun 2017 13:38:56 GMT

Try like this :

Add an additional column with the source info as below , when want only data from one table apply this filter and you will get it

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
EU:

SQL SELECT

'EU' as Source ,

CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF

FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
EUX:
SQL SELECT

'EUX' as Source ,

CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

Re: Issue with Loading data from two databases

Qlik_Enthu — Mon, 19 Jun 2017 13:47:28 GMT

Ronald is there any ref. manual for source-field ? please share if u have

Re: Issue with Loading data from two databases

RonaldDoes — Mon, 19 Jun 2017 14:08:25 GMT

Hi Saimeenakshi,

I'm not sure I understand you.

We are just adding a field (named "Source" but could be just as well "WhichDB") to every record we load. In all records from the first DB, we fill it with "EU" (because that is what you named your first table, but it could be anything). In all records from the second DB, we fill it with "EUX".

Re: Issue with Loading data from two databases

Qlik_Enthu — Mon, 19 Jun 2017 14:11:53 GMT

ohhhhh! I got you meant "Qualify" and "Unqualify" clauses

Re: Issue with Loading data from two databases

RonaldDoes — Mon, 19 Jun 2017 14:33:46 GMT

There you go!

Do you Qualify?- How to use QUALIFY statement