topic 2 Database connections and a where clause using both in QlikView

2 Database connections and a where clause using both

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Hope someone can help.

I am looking to use data from two different database connections and utilise one set of data I pull in the where clause of the second set of data.

I understand that you can’t have two connections open at the same time but I can pull one data source and then the other and then join the tables. However in my second data set I need to apply a where clause from my first data set. This would be because otherwise the second data pull would be incredibly large as Table 2 contains every possible date occurrence and each different date the ID could be aligned to a different person. (I have a separate table for people which I have not included below)

I need to do something like the below:

Database Connection 1

Table 1:

DataField

right join(Table 1)

Database Connection 2

Table 2:

Product

Date of Product

Where (Datefield = Date of Product)

Re: 2 Database connections and a where clause using both

qlikviewwizard — Mon, 06 Aug 2018 08:56:48 GMT

Hi,

Try like this.

Merging Data from Multiple Sources

ODBC CONNECT TO <Database 1>;

SQL SELECT * FROM Customers;

ODBC CONNECT TO <Database 2>;

Concatenate SQL SELECT * FROM Customers;

Re: 2 Database connections and a where clause using both

Or — Mon, 06 Aug 2018 09:12:30 GMT

First, consider loading the full second table and then joining in QlikView, if possible. Even if the table is large, this shouldn't be too big a problem. However, I understand that this may not be feasible, in which case...

I'm not sure if it's feasible for large data volumes (it may be even slower than loading the full data depending on your data sources), but you could loop-load the second connection for each line in the first connection. The code I've included may not be entirely accurate, but you should be able to adapt it from Loops in the Script if necessary.

Database Connection 1

Table1:

Load

DateField

ID;

For vRowNum= 1 to NoOfRows('Table1')

Let vWhereClause = Peek('DateField',vRowNum-1,'Table1');

Load

Product

Date of Product

Where $(vWhereClause)= Date of Product;

Next vRowNum

Re: 2 Database connections and a where clause using both

pooja_prabhu_n — Mon, 06 Aug 2018 09:34:39 GMT

Hi,

Try using Exists function:

Database Connection 1

Table 1:

DataField

right join(Table 1)

Database Connection 2

Table 2:

Product

Date of Product

Where Exists(Datefield, Date of Product)

Thanks,

Pooja