Issue with Loading data from two databases

[Qlik_Enthu]

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 ?

[antoniotiman]

Hi,

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

[sunny_talwar]

Try this

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:

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

[RonaldDoes]

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

[antoniotiman]

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 ?

[antoniotiman]

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,......

[datanibbler]

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

[Qlik_Enthu]

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.

[RonaldDoes]

Hi Saimeenakshi,

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

That seems to suit your requirement perfectly to me.

1. ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW); 
2. TableName: 
3. LOAD 
4. 'EU' AS Source, 
5. *; 
6. SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF 
7. 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'); 
8.  
9. ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE); 
10. Concatenate (TableName): 
11. LOAD 
12. 'EUX' AS Source, 
13. *; 
14. SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF 
15. 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'); 

[sunny_talwar]

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