Inner join SQL dataset to QVD

[mariusvr]

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

[treysmithdev]

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;

[mariusvr]

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