Using data from a QVD file in Oracle SQL query

[#1]

Hello,

Im trying to create a Qlikview model that will work in a near real time enviroment. Im running Qlikview on an Oracle 11g database, the table Im selecting from is a very big table that has over 5000000 new records every hour - and I need to look at a complete day in my model.

Since I need the report to be able to refresh very quickly, and the table becomes huge as the day goes by, selecting data from the whole table is out of the question. I came up with the following process:

1. Each day at 0:00 Im deleting all previous QVD files. The Im running the query on the raw table (at 0:00 I have very little records so it's not a problem to run it). Then Im saving the data to 2 separate QVD files - data.qvd that holds the data results and max_time.qvd that holds only the max record time that only has the max record time for the previous run.

2. Every 5 minutes I want to run the query using the time from max_time.qvd as start time, then concatenate the data to the previously stored data.qvd file, then overwrite max_time.qvd to hold the new max time.

I have managed to work out all the steps of the process but one - I cant work out how to run the sql query with the data from max_time.qvd in the where clause.

Does anyone know how to do this (if it can be done at all)?

Thanks for your time

Yoav

[Anonymous · Accepted solution]

Hi Yoav,

In this example there's a Max_Load_Time.qvd created with just one row of data, the last load time.

MaxLoadTime:
LOAD Last_Run_Time
FROM
Max_Load_Time.qvd
(qvd);

Let vMaxTime = Peek('Last_Run_Time',0,'MaxLoadTime');

SQL Select
*
From TABLENAME where Date>$(vMaxTime);

What I do here is to load the value into a variable using Peek() and then use the variable in the SQL select statement. There might be some small syntax errors but basically it should work.

[#3]

Hi Johannes,

It worked nicely.

Thank you 🙂