Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Perhaps stevedark has by now found an answer to that question.
It was actually Rob Wunderlich 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
If your applications are set to generate a logfile (Document Properties/Generate logfile) the files you need are beeing stored besides your apps
Another aproach is contact your DBA, generally they have tools to locate queries that are blocking each other
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
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
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
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.