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.
Not sure that I understand your question. It certainly is easy enough load the results from the query into QV
CONNECT TO <connection string>;
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?
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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
SQL SELECT select max(exchange_rate) as rate,
YEAR(created) as year,
MONTH(created) as month,
DAY(created) as day
from transaction where currency_id>1
group by YEAR(created),
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