SQL select in Load from DB or from QVD files?

[#1]

Hi all,

I have a relatively complicated SQL SELECT statement which works fine when I do a select using MySQL directly on a database.

The SELECT is as follows:

select max(exchange_rate) as rate, currency_id, YEAR(created) as year, MONTH(created) as month, DAY(created) as day from transaction where currency_id>1 group by YEAR(created),MONTH(created),DAY(created), currency_id;

...I've attached an image of the resulting fields as viewed by MySQL Workbench.

I would like to have access to this resulting table in my Qlikview document.

My question is whether I perform this select in the script which creates the QVDs? Or whether I should attempt to create this table using fields from within the QVDs?

All the necessary fields are already available to me in the QVDs as part of the transaction table. But I would like to make a new table called currency_rates which contains only the result of this SQL SELECT.

Any help would be greatly appreciated.

Thanks

Peter

[#2]

Is what I'm looking for here impossible to achieve with Qlikview?

[#3]

I would create such a table in the script that creates the QVD's and store it as a seperate QVD.

You can (and should) read from the already created qvd wich contains the transaction data. This will be faster than going over the sql connection to your ERP(?) database.

Regards,

Yves

[jonathandienst]

Peter

Not sure that I understand your question. It certainly is easy enough load the results from the query into QV

CONNECT TO <connection string>;

LOAD *;

SQL <your select statement here>;

Build the connection string using the wizard in the script editor.

As to whether you get the data from the query or from existing QVDs, that's a business/data architecture question that I could not answer here with the information and time available. But maybe I misundertood you?

Regards

Jonathan

[#5]

As you already have your select statement ready you might as well include it within your query that created teh QVDs. You can name tables so just make sure the query is done as follows

CONNECT STRING

currency_rates:

SQL SELECT select max(exchange_rate) as rate,

currency_id,

YEAR(created) as year,

MONTH(created) as month,

DAY(created) as day

from transaction where currency_id>1

group by YEAR(created),

MONTH(created),

DAY(created),

currency_id;

This will create the table currency_rates based on your connection string above .

QVDs save on loading time as that's what tehy are designed for. So best to have all the 'complicated stuff' taking place before hand as this means you can do a straight load of all the data. This would require seperate documents one for the QVD creation and your actual QV dashboard. And that depends on how you structure your reloading. but that's another issue