Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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;
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
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