topic Re: Code to export script in QlikView

Code to export script

Mon, 16 Sep 2013 12:34:08 GMT

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 — Mon, 16 Sep 2013 13:49:58 GMT

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 — Mon, 16 Sep 2013 18:13:33 GMT

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

Re: Code to export script

stevedark — Mon, 16 Sep 2013 20:08:25 GMT

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/

Re: Code to export script

Clever_Anjos — Mon, 16 Sep 2013 20:11:25 GMT

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

Clever_Anjos — Mon, 16 Sep 2013 20:15:43 GMT

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

Re: Code to export script

stevedark — Mon, 16 Sep 2013 20:18:07 GMT

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

Tue, 17 Sep 2013 08:48:25 GMT

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

Clever_Anjos — Tue, 17 Sep 2013 12:25:40 GMT

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

Re: Code to export script

stevedark — Tue, 17 Sep 2013 15:22:03 GMT

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

Fri, 20 Sep 2013 09:35:47 GMT

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

Fri, 20 Sep 2013 09:36:20 GMT

Awesome Clever! Thanks for the very handy tip!

Re: Code to export script

Manish_Kumar_ — Sat, 27 Aug 2022 18:06:59 GMT

Hi Author

Just wondering if i could get this code ..would sought out a major problem!

Use case: i want to find that out of 200 qlik applications, how many are using a particular qvd ..This is because that qvd will not longer be continuing a new qvd with new fields will be replacing it..

It could be a major use case to other organisations as well!!

So any kind of technical help would be appreciated! Rest your concept seems good!! @Author