topic Inner join SQL dataset to QVD in App Development

Inner join SQL dataset to QVD

mariusvr — Fri, 18 Oct 2019 09:06:17 GMT

Hi,

I work with large datasets and my data-load therefore times out. To better manage this I want to create a reference QVD files and use this to restrict the dataset for subsequent SQL queries.

reftable:

SQL SELECT NHI,Encounter from table1 INP
INNER JOIN table2 PID ON (INP.NHI = PID.EXTERNALID ) ;

newtable:

SQL select * from table3 EXAM INNER JOIN reftable REF ON EXAM.encounter = REF.encounter.

What is the best way to achieve this in Qlik?

Thanks, Marius

Re: Inner join SQL dataset to QVD

treysmithdev — Sat, 19 Oct 2019 19:10:11 GMT

Hi, Qlik passes a query to the database, the database then executes it and returns the resulting data back to Qlik. Data within the Qlik application cannot be used as a reference or intersection for operations in the database.

If you want to completely move this process QVDs, you could do something like this:

Ref: Load NHI as EXTERNALID From [lib://QVD/INP.qvd](qvd); Inner Join(Ref) Load EXTERNALID, ENCOUNTER From [lib://QVD/PID.qvd](qvd); Table: Load * FROM [lib://QVD/EXAM.qvd](qvd) Where Exists(ENCOUNTER); Drop Table Ref;

Re: Inner join SQL dataset to QVD

mariusvr — Fri, 25 Oct 2019 06:15:02 GMT

Thank you for your fast reply.

My solution ended up being simplifying my SQL queries a lot and using the power of Qlik to further stratify the data later.

Another option would have been to save the Qlik data as a csv file and using the SQL bulk insert function (although this is disabled on our database)

Thanks, Marius