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