Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Code to export script

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

12 Replies
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

Gysbert_Wassenaar

Perhaps stevedark has by now found an answer to that question.


talk is cheap, supply exceeds demand
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

Clever_Anjos
Employee
Employee

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

Clever_Anjos
Employee
Employee

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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


Clever_Anjos
Employee
Employee

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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.