11 Replies Latest reply: Sep 20, 2013 5:36 AM by Byron Van Wyk RSS

    Code to export script

    Byron Van Wyk

      Hi Guys and Gals,

       

      We are having a problem with one of the SQL scripts written inside a QlikView application. The problem is, is that one of the scripts is locking tables within our database. We have over 200 QlikView dashboards and so going through each one is not really an option.

       

      What I would like to know is if it is possible to read script tabs and copy all information from them and paste into another file like a .txt or .csv

       

      I can create the loop that will read each QlikView application, just need to know if the above can be done and what the code for this piece would be

       

      Many thanks,

      Byron

        • Re: Code to export script
          Marcus Sommer

          Here a example from APIGuide.qvw:

           

          rem ** add new line to script **

          set docprop = ActiveDocument.GetProperties

          docprop.Script = docprop.Script & "Load * from abc.csv;"

          ActiveDocument.SetProperties docprop

           

          Reading were so:

          Dim oldScript

          set docprop = ActiveDocument.GetProperties

          oldScript = docprop.Script

           

          I suggest that you such redundant code rather handled with include-variables and you must in the future only change a single txt-file. Before search and replace any code with macros make a BACKUP!

           

          - Marcus

          • Re: Code to export script
            Gysbert Wassenaar

            Perhaps Steve Dark has by now found an answer to that question.

            • Re: Code to export script
              Steve Dark

              It was actually rwunderlich who came up with a solution for me, with an solution based around his Document Analyzer that exported the script from all .qvw files in a folder.  I don't know if he has made the solution generally available.

               

              Just a couple of thoughts on the original point - using the NO LOCK hint on the SQL query may help things, also reducing the concurrency on reloads in Publisher (or Server) may help.  Time spent optimising indexes in SQL can also pay dividends.

               

              Regards,

              Steve

               

              http://www.quickintelligence.co.uk/

              • Re: Code to export script
                Clever Anjos

                If your applications are set to generate a logfile (Document Properties/Generate logfile) the files you need are beeing stored besides your apps

                  • Re: Code to export script
                    Steve Dark

                    The log file contains quite a lot of the script in it - but it is not the same as exporting the load script (which can be done from within the script editor).  Notably if you have a loop in your load script the code within that loop is written to the log file many times.

                     

                    - Steve

                  • Re: Code to export script
                    Clever Anjos

                    Another aproach is contact your DBA, generally they have tools to locate queries that are blocking each other

                    • Re: Code to export script
                      Byron Van Wyk

                      Hi All,

                       

                      Thanks for all your responses. I will review and reply to each one

                      We figured out a solution to do this. This was our approach. I will post the code as soon as I am back in the office

                       

                      1) Create a loop for each QlikView document using the filelist function.

                      2) The loop would contain an EXECUTE command that creates a prj folder for each document  using MKDIR

                      3) Each document unfortunately needs to be opened and saved for the prj folder to actually create the log files and document properties. This was also done in script, but it does take a bit of time for those slightly large qvw

                      3) In the prj folder is a .txt file with the name load script

                      4) Create another loop to retrieve each load statement script as a field with another field representing the document it came from

                      5) Once the data is loaded, create a table box object and search for the code that creates the lock

                      6) The SQL DBA already knows what code is creating the locking, so all we had to do was tell him which report contains it

                       

                      Just happy we got this right and learnt a lot through the process. However, the above is not a practical solution. I also want to review the QlikView governance app which from what I heard would also contain scripting information in the front end for review. Have to have a look and see

                       

                      Once again, thanks for all your responses and will reply through the course of the day

                       

                       

                      Cheers,

                      Byron

                       


                        • Re: Code to export script
                          Steve Dark

                          Hi Byron,

                           

                          That's a neat solution.  The parsing of the script files could have been done without using QlikView as there are lots of tools for searching text files (I use a lightweight one called SR32, or Notepad++ can do this).

                           

                          Thanks for sharing.

                            • Re: Code to export script
                              Byron Van Wyk

                              Thanks for your approval Steve Means a lot coming from you! Thanks for the tip, and yes I definitely could have used Notepad++, will go check out SR32

                               

                              Regarding the code for all to see, please bare with me! I have been a little inundated but since I love spending all my time on QlikView, will definitely upload a sample document this weekend for all to review

                               

                               

                              Cheers,

                              Byron

                          • Re: Code to export script
                            Clever Anjos

                            If you are using QVD files, there´s a trick.

                             

                            QlikView stores your SQL into XML header of each file.

                             

                            You can open each one (or create a loop to process each qvd) into XML mode and import "Lineage" table

                             

                            webview.png