4 Replies Latest reply: Sep 24, 2012 8:17 AM by aeori007 RSS

    SQL select in Load from DB or from QVD files?

      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

        • Re: SQL select in Load from DB or from QVD files?

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

          • Re: SQL select in Load from DB or from QVD files?
            Jonathan Dienst

            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

            • Re: SQL select in Load from DB or from QVD files?

              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