Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Comparing data from two different data sources

Hi all,

I'm trying to compare running times of benchmark tests which are stored in two different databases using charts. The data structure as well as the names of the fields are identical in each of the databases.

For example, I have the following tables containing the fields below:

TESTCASE
  TC_Id
  TC_Nr
  TC_Name
 
TESTCASE_RUN
  TCRun_Id
  TC_Id
  TC_Duration

I thought, the best thing for me would be to have the tables of each database in two different namespaces. So I used labels to declare namespaces. In the edit script I used the following code:

<code>
ODBC CONNECT32 TO [Database1] ...

QUALIFY *;
DB1:
SQL SELECT * FROM [Database1].TESTCASE

DB1:
SQL SELECT * FROM [Database1].TESTCASE_RUN

ODBC CONNECT32 TO [Database2] ...

DB2:
SQL SELECT * FROM [Database2].TESTCASE

DB2:
SQL SELECT * FROM [Database2].TESTCASE_RUN
</code>

This does not have the effect I want to achieve, since:

* On creating a new sheet object the following fields are shown:
  - DB1.TC_Id
  - DB1.TC_Nr
  - DB1.TC_Name
  - DB1-1.TCRun_Id
  - DB1-1.TC_Id
  - DB1-1.Duration
  - DB2.TC_Id
  - ...
* The tables of DB1 and DB1-1 are not connected, as they would be without using labels and the QUALIFY statement.

Does anybody has an idea how to import the tables from the different databases into different namespaces? I've also tried to rename the single fields in the script, didn't work either (Message: TC_Id already exists).

Thanks in advance!

1 Solution

Accepted Solutions
MVP
MVP

Re: Comparing data from two different data sources

Hi

because the fields in TESTCASE and TESTCASE_RUN are not the same, you cannot rely on auto-concatenation. This should solve your problem:

QUALIFY *;

DB1:

SQL SELECT * FROM [Database1].TESTCASE

Concatenate(DB1)

SQL SELECT * FROM [Database1].TESTCASE_RUN

Personally, I would not load the data into two separate namespaces, I would rather join them in a single table and use a field to indicate the source. Something like

CONNECT ... to DB1 ...

Data:

LOAD *,

          'DB1' As Source;

SQL SELECT * FROM [Database1].TESTCASE

 

Concatenate(Data)

LOAD *,

          'DB1' As Source;

SQL SELECT * FROM [Database1].TESTCASE_RUN

CONNECT ... to DB2 ...

Concatenate(Data)

LOAD *,

          'DB2' As Source;

SQL SELECT * FROM [Database1].TESTCASE

Concatenate(Data)

LOAD *,

          'DB2' As Source;

SQL SELECT * FROM [Database1].TESTCASE_RUN

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
2 Replies
MVP
MVP

Re: Comparing data from two different data sources

Hi

because the fields in TESTCASE and TESTCASE_RUN are not the same, you cannot rely on auto-concatenation. This should solve your problem:

QUALIFY *;

DB1:

SQL SELECT * FROM [Database1].TESTCASE

Concatenate(DB1)

SQL SELECT * FROM [Database1].TESTCASE_RUN

Personally, I would not load the data into two separate namespaces, I would rather join them in a single table and use a field to indicate the source. Something like

CONNECT ... to DB1 ...

Data:

LOAD *,

          'DB1' As Source;

SQL SELECT * FROM [Database1].TESTCASE

 

Concatenate(Data)

LOAD *,

          'DB1' As Source;

SQL SELECT * FROM [Database1].TESTCASE_RUN

CONNECT ... to DB2 ...

Concatenate(Data)

LOAD *,

          'DB2' As Source;

SQL SELECT * FROM [Database1].TESTCASE

Concatenate(Data)

LOAD *,

          'DB2' As Source;

SQL SELECT * FROM [Database1].TESTCASE_RUN

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Comparing data from two different data sources

Hi Jonathan,

thanks a lot for your quick response.

Your second suggestion is exactly what I need.

Cheers,

Johannes