Include qvd field in Sql query

[DirkCtz]

Hi,

I pull +-2000 records for the first table and need to join to another table with millions of records.

How do I join and only pull the info from the millions for ONLY the 2000 records and not the entire millions?

and these are two different databases the info comes from.

In short - This would be a simple left or inner join in SQL.

[Tab 1]:

Connect to database 1;

Select *
from db.table;

join

[Tab 2]:

connect to database 2;

Select *
from db.tables
where unique_id is in database 1 only…… just writing out what I have in mind. How do I do this?

ie I only want to pull records relevant to tab 1 and not all records in Tab 2.

Or if I do qvd file for Table 1, how do I pull only the unique_id from the qvd file in a select/load where Im querying the second database?

hope Im making sense here

[marcus_sommer]

You may store the relevant information from Tab1 within a variable and using them within the query for Tab2 in a where clause. If your id's are more or less consecutive and/or you there are some date/time information you may be able to create any >= or <= logic. Even if you couldn't complete exclude some overlapping of the data and/or pulling more data as needed it might be sufficient to reduce the dataset of the second query significantly.

If not such logic is applicable you could try to transfer the id's within a list and filtering with something like: where id = in('$(idList)'); But quite often the in() is restricted to a certain amount of parameter-items and it's surely not very performant to filter millions of records against a list of thousands.

Beside this you may pull the entire table from the second query without joining it directly else only the filtered dataset is then used for then join. In this regard you may implement some kind of an incremental approach for this query.

If none of this is really practicably you should transfer the filter-set directly from DB1 to DB2 and/or from Qlik to the DB2 - for example by storing it as a simple csv which is then directly loaded from the DB2 as an own table and afterwards used for an inner join filtering. Usually that's not a technically challenge because each DB should support it else you will just need the proper permissions to load external data into the DB.  

- Marcus

[NadiaB · Accepted solution]

Hi @DirkCtz 

It will probably more recommended to use ODAG, you can use it from the UI

https://www.youtube.com/watch?v=Ft3I00-g4H4

But for something more dynamic there is also an API

https://help.qlik.com/en-US/sense-developer/June2019/Content/Sense_Helpsites/ODAG-Service-API.htm

Hope it helps.

[DirkCtz]

Hi
Apologies for my delay in responding - gave up on this item, but now giving it a shot again.

My first table might have less than 10,000 records whereas my second table has 40million records. I do not want to pull the entire 40 million records every day to enrich the 10,000 records.

I thought of saving the account numbers from the first table into a qvd then reference those in the Table 2 query?

The list option is limited to 1000 records at a time... Which makes it cumbersome.

Is there a way only obtain records from the 2nd table (in another server) using only the account numbers in the first table?
How do I select the account numbers from the qvd to use in a SQL on Qlikview? Possible?

[marcus_sommer]

Like most tools does Qlik don't perform any SQL itself else it transferred the statement per driver to the db and received on this way the results again. This means you could not match in any way results from Qlik with the SQL unless you could put it into variables which content is also transferred to the db (like above mentioned in() example which seems to be not very suitable in your use-case).

Therefore again my suggestion to bring the Qlik result into the db per csv-store from Qlik and an appropriate import from the db and using it then as inner join filter. Principally it's also possible to write the results from Qlik into the db and therefore to avoid this detour - but it adds usually more complexity so that my suggestion should be easier to develop.

Beside this you should also consider to pull all 40 M records with an incremental approach to this table and applying your real extracting afterwards. 

- Marcus