2 Database connections and a where clause using both

[alwayslearning]

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

ID

right join(Table 1)

Database Connection 2

Table 2:

ID

Product

Date of Product

Where (Datefield = Date of Product)

[qlikviewwizard]

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;

[Or]

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

ID

Product

Date of Product

Where $(vWhereClause)= Date of Product;

   Next vRowNum

[pooja_prabhu_n]

Hi,

Try using Exists function:

Database Connection 1

Table 1:

DataField

ID

right join(Table 1)

Database Connection 2

Table 2:

ID

Product

Date of Product

Where Exists(Datefield, Date of Product)

Thanks,

Pooja