Sunday, August 31, 2008

QlikView Command Line and Automation

This posting is for the more technically inclined among you and developers who work with reports that must reload automatically.

You can run QlikView from the command line. You've probably created shortcuts on your desktop to various useful QV reports. In that kind of shortcut the command is simply the complete pathname to the report file. That will also work in a .bat or .cmd file; just enter a usable pathname to the report file and Windows will open QlikView for the report.
More interesting things can be done with a command that includes the pathname to the QlikView program followed by a pathname to the report file. This command line syntax allows you to enter switches or parameters. The command line syntax is covered pretty well in the reference manual. For example: If the pathname to your QlikView program is c:\Program Files\QlikView\QV.exe and the report you want to open is c:\reports\Inventory.qvw then this line will do a simple open of the report in a shortcut or .bat or .cmd file:
"c:\Program Files\QlikView\QV.exe" c:\reports\Inventory.qvw

You may or may not need the quotes around the pathname for QV.exe or for your report file. Windows usually wants to have the quotes for any pathname containing a blank or special character.

If you add a /r switch (notice the front-leaning slash) then the command will open the report, run the reload function (executing the loadscript), and then save and close the report:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /rp will do something similar except it runs the partial reload function.

The /l switch (that's a lower case ell) will open the report, run the reload function and leave the report open. This one can be useful as a desktop shortcut:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /p switch will open the report, run a partial reload and then leave the report open.

The /v switch can be used to pass a document variable value into the report. It can be used either with or without the switches described above. The document variable might be used in macro module code or in the loadscript. In our example if we want to reload the QV report and pass in the document variable batch_flag with a value of 1 then we'd use a command like this:
"c:\Program Files\QlikView\QV.exe" /r /vbatch_flag=1 c:\reports\Inventory.qvw

[The previous line and all of the previous command line examples are intended to be a single line even if your browser is breaking them into two lines]

We often use a command like that for reports that work differently when they are executed in batch versus being opened by a user.

My personal favorite way to use the command line syntax is from a VBScript file (.vbs script) on the Windows PC. VBScript allows me more flexibility in automatically figuring out which report file to run and what kind of document variable values should be passed in. From the VBScript file the syntax might look something like this: (using the same example)
Set objShell = CreateObject("WScript.Shell")
objShell.Run """c:\Program Files\QlikView\QV.exe"" /r /vbatch_flag=1 c:\reports\Inventory.qvw"


[Set objshell... is a single line and objShell.Run... is the second line no matter how your browser is breaking them up]

In addition to filenames using drive letters you can use network file names like this: \\canserver\projects\reports\Inventory.qvw

29 comments:

Ramunas Balukonis said...

Hi, Tim
This is great article. But when you write about \r switch, this is not always true: "If you add a /r switch (notice the front-leaning slash) then the command will open the report, run the reload function (executing the loadscript), and then save and close the report:".
Suppose you do manual reload using Ctrl+R inside QV, but you turn off option "close when finished". I like to turn off this option (especially with long running reload), review the log after reload, puch "close" button. After this manual reload you started reload with command prompt: qv.exe /r myqvfile.qvw, but now the same log window remain opened and you have manualy close the window. So, this method not always good for automating reloading process, lets say from sql server job. Maybe you known another way how to automate reload process?

Ramunas Balukonis

-TB said...

Hi Ramunas;

For the reports that we reload, unattended, on a server:

We use a Windows scheduled task that executes a .bat script.

The .bat script starts QlikView for a particular report file and passes in a value of 1 for a report variable named batch_flag.

The report has a macro defined that executes on the file open event. So, every time the report opens this macro runs. The macro knows that it has been executed in batch by the value of batch_flag.

The macro resets the variable to zero and does a reload. It checks to see if the load script had any errors and if there were none then it does a save-as to store the loaded report to a shared disk where business users have access to it. (this is simplified a bit). The macro looks something like this:

Sub MAIN
set bvar = ActiveDocument.Variables("batch_flag")
batch = bvar.GetContent.String
if batch = "1" then
'set batch_flag to 0 so as to prevent macro from running on user open
bvar.SetContent "0",true
ActiveDocument.Reload
Set objvar = ActiveDocument.Variables("ScriptErrorCount")
If trim(objvar.GetContent.String) <> "0" then
ActiveDocument.GetApplication.Quit
Exit Sub
End If
ActiveDocument.SaveAs fname_report
ActiveDocument.GetApplication.Quit
else
ActiveDocument.Sheets("Main").Activate
end if
End Sub

I don't know if that will solve your problem with the window that doesn't close. It's how we reload reports on a server. Let me know if that works for you.

Ramunas Balukonis said...

Hi, Tim

this is a interesting solution. Before I have tried similar solution with ReloadEx (this method has several additional parameters, one of them suppress progress dialog). This is my example outside QV file (saved in reload.vbs):


Set qlik = CreateObject("QlikTech.QlikView")
Set Doc = qlik.OpenDoc(WScript.Arguments(0))

'*********************** Variables *****************************
Set vDateLeft = Doc.Variables("vDateLeft")
vDateLeft.SetContent WScript.Arguments(1), true
Set vDateRight = Doc.Variables("vDateRight")
vDateRight.SetContent WScript.Arguments(2), true
Set vFileName = Doc.Variables("vFileName")
vFileName.SetContent WScript.Arguments(3), true
'*********************** Variables *****************************

Doc.ReloadEx 2, 1
Doc.Save
Doc.CloseDoc
qlik.Quit
Set qlik = Nothing


This script takes 4 parameters: qvw file to open, DateLeft, DateRight and QVDFileName. Thre is my QV file reload script:

MyTable:
LOAD
....
SQL SELECT ....
FROM v_MyView
WHERE [Date]>=$(vDateLeft) AND [Date]<=$(vDateRight);

STORE MyTable into MyTable.$(vFileName).qvd;

DROP TABLE MyTable;

But using this script (reload.vbs) it is impossible to run reload for more than 1 file in parallel. I run several reloads in parallel, but see only one process working. Did you tried to run you script from several files the same time? It would be a solution for me.

Thanks,
Ramunas

-TB said...

You can probably run two or more QV reloads at the same time on a server by running them in separate Windows accounts. Try setting up two scheduled tasks that would run a minute apart but define them with different Windows IDs and passwords.

I never tried to run more than one at a time on purpose. The QV reload takes up so much of the processor that running two of them at the same time might not save much time (but, like I said, I never tried it!).

Thanks for your comments. I didn't know you could run QlikView object methods from a vbs script like that. Where did you learn how to do that?

-TB said...

For anyone interested-- some of this automation functionality that Ramunas is describing is documented in the QlikView Automation Reference.pdf document that is installed on your computer along with a FULL installation of QlikView.

Adi Ben-Ari said...

Any idea how to run QV.exe in silent mode - so no Windows even if there's an error?

-TB said...

Adi Ben-Ari,

You need to include the line
Set ErrorMode=0;
at the top of your loadscript. That tells QlikView to ignore errors and just keep processing the loadscript. If you do that it will prevent QlikView from opening up an error dialog window but you will need to check for errors in some other way. Look at this posting for a review of that topic: http://qlikviewmaven.blogspot.com/2008/09/checking-for-errors-when-report-reloads.html

Marc said...

Hello, Great work all you guys who make knowledge availabel do...
Could someone help me with a problem I am struggling with.
I have used the command line /r in a Windows Scheduler (XP) to reload a document (desktop version of Qlikview) and then in combination with a macro send out some reports in PDF (all of this unattended). I am not a specialist but got this working fine. Now after I installed QV9 SR6 I get the problem that wihtin Qlikview the reload and combined Macro is still working as it should but in Windows Scheduler I get an error that QV had to shut down etc.
Anybody any idea?

regards,

Marc

Anonymous said...

Sorry, only an info, but if i have a batch file that open 2 .qvw, qlickview open only one session with two dashboard into...so how can i open 2 dashboard in 2 different executable file?
i would like open 2 different files in 2 different windows so i could tab ....

Umbe said...

Sorry, only an info, but if i have a batch file that open 2 .qvw, qlickview open only one session with two dashboard into...so how can i open 2 dashboard in 2 different executable file?
i would like open 2 different files in 2 different windows so i could tab ....

Cj said...

Hi,

I'm trying to do something similar with this. I've managed to get to a point where I can run a batch script to reload the report I need and export a table box to a file using a macro.

The third, and more problematic issue, is then sending this via email as an attachment.

I have managed to get some script to do this and it works when I have the QlikView client open. Unfortunately it doesn't when I reload the report from the batch script. Do you (or anyone!) have any ideas why this would be?

Thanks,

Chris

-TB said...

Hi Chris,
I've never worked with the email notification feature myself (so far, there hasn't been a need for it). You would probably have better luck posting your question to the much larger audience in QlikCommunity (if you haven't already).

Jonas Sandström said...

HI EVERYONE!

Here are two solution to running multiple QlikView instances, without using separate accounts:

start "" "C:\Program Files\QlikView\Qv.exe" /r C:\path\file.qvw

or use this

call "c:\program files\qlikview\qv.exe" /R "C:\path\file.qvw"

This will call QlikView in a new instance every time.
Feel free to add this to the original post :)

Nerver Say No said...

Hello Tim,

This is good one very useful article
and my question i have list ox having data like 2,4,6,8 etc... as Hours..,
i need to update the my batch file as per selected hours...
i mean if i am selecting 2 the batch file will be run afters 2 hrs..
how it is possible.
plz help me
Many Thanks
vays

Fuad Aslanov said...

Great article, thx.

But we have problems.

We have script written to be executed on OnPostReload.
If we do it manually the following script creates excel file on certain folder, everything is good.
But when we run
c:\Program Files\QlikView\QV.exe" /r c:\reports\Cs.qvw

excel file is generated but Excel remains open and files not renamed and put to appropriate folder.

can we somehow solve it?
public sub Export_to_Excel

Dim oExcelObject, sResultFileName

Set oExcelObject = Nothing
set oExcelObject = CreateObject("Excel.Application")

oExcelObject.Visible = False
oExcelObject.DisplayAlerts = False
dCurrentTimeStamp=Now()-1


sFilePath = "\\192.168.1.111\Reports\\Daily\"
sResultFileName = "Cs_Daily_" & LPadDigit(Day(dCurrentTimeStamp)) & LPadDigit(Month(dCurrentTimeStamp)) &_
LPadDigit(Year(dCurrentTimeStamp)) & ".xls"


set s = ActiveDocument.GetSheetObject("TB01")
s.SendToExcel
oExcelObject.ActiveWorkbook.Activesheet.Name = LPadDigit(Day(dCurrentTimeStamp)) & "." & LPadDigit(Month(dCurrentTimeStamp)) &_
"." & LPadDigit(Year(dCurrentTimeStamp))
oExcelObject.ActiveWorkbook.SaveCopyAs sFilePath & sResultFileName
oExcelObject.ActiveWorkbook.Saved = True 'it is needed to prvent save prompt
oExcelObject.ActiveWorkbook.Close
oExcelObject.Quit
Set oExcelObject = Nothing

end sub

Fuad Aslanov said...

Great article, thx.

But we have problems.

We have script written to be executed on OnPostReload.
If we do it manually the following script creates excel file on certain folder, everything is good.
But when we run
c:\Program Files\QlikView\QV.exe" /r c:\reports\Cs.qvw

excel file is generated but Excel remains open and files not renamed and put to appropriate folder.

can we somehow solve it?
public sub Export_to_Excel

Dim oExcelObject, sResultFileName

Set oExcelObject = Nothing
set oExcelObject = CreateObject("Excel.Application")

oExcelObject.Visible = False
oExcelObject.DisplayAlerts = False
dCurrentTimeStamp=Now()-1


sFilePath = "\\192.168.1.111\Reports\\Daily\"
sResultFileName = "Cs_Daily_" & LPadDigit(Day(dCurrentTimeStamp)) & LPadDigit(Month(dCurrentTimeStamp)) &_
LPadDigit(Year(dCurrentTimeStamp)) & ".xls"


set s = ActiveDocument.GetSheetObject("TB01")
s.SendToExcel
oExcelObject.ActiveWorkbook.Activesheet.Name = LPadDigit(Day(dCurrentTimeStamp)) & "." & LPadDigit(Month(dCurrentTimeStamp)) &_
"." & LPadDigit(Year(dCurrentTimeStamp))
oExcelObject.ActiveWorkbook.SaveCopyAs sFilePath & sResultFileName
oExcelObject.ActiveWorkbook.Saved = True 'it is needed to prvent save prompt
oExcelObject.ActiveWorkbook.Close
oExcelObject.Quit
Set oExcelObject = Nothing

end sub

-TB said...

Hi Fuad,
I suspect the /r is making QlikView close down right after the reload without giving your macro enough time to finish.

You could try writing a macro to be executed On Open that does the reload, export to Excel, and then close. It will need to be executed with a particular variable value that tells the macro it is ok to run otherwise the macro will run every time you open the document. (when you do that the first thing the macro must do is change the value of the variable!) I think I described something like that in one of the old blog posts.

M&M said...

Hey guys I'm using a batch file to reload a qlikview document (because I need to execute a macro after an onpostReload event).

I don't know why if I check the option "run only when user is logged on" it works fine.

But if I check the option "Run whether user is logged on or not" it executes but does nothing.

Anyone knows what could it be the problem??

I've heard that a posible problem is that you need to change to "mapping adresses". I mean change C:\users\myBat.bat for \\qlikview\myBat.bat.

But it still don't work.

Many thanks in advance!!

TZCARMY said...

the /I should work here

Anonymous said...

Thanks for the article Tim. Have you found a way to batch script a /nodata reload and save the file with no data ?

We have several hundred files which we want to load with no data as "backup" files. I am hoping to avoid adding macro's to each file.

Thank, Tod

Anonymous said...

Hi,
I amd having 3 qv documents for reload a single batch file and it is reloading fine. Sometimes I have found that after starting batch file, first qvw starts to reload in between cmd.exe get crashed automatically and it is not available in task manager due to which other sequential both qvw won’t run (first is already started so it will run successfully without any issue).


Anybody has experienced this issue or what might be the reason?


Thanks in advance.


Regards,

Arunesh

Jacob B said...

Hi Gurus,
Could you please help me.
My requirement is to trigger Qv QMC tasks in batch instead of current QMC task time based scheduling. And I should be able to monitor the reloads in QMC. I tried /r option but it is trigerring the reload in Client and not in the Server (In a new window).

-TB said...

Hi Jacob B,
If you haven't done it already, you should post your question in QlikCommunity. Over there, it is more likely to be seen by someone familiar with the issue who can supply a solution for you.

-Tim

Unknown said...

Hi Tim, concerning your second comment, which begins with
"We use a Windows scheduled task that executes a .bat script.

The .bat script starts QlikView for a particular report file and passes in a value of 1 for a report variable named batch_flag"

Thanks a lot, it works like a charm !
We have batched (with .bat) the export + send over FTP for many qvw applications. We have scheduled the .bat on our qlikview server, in which we installed a QV client.

Adrien Allard

Anonymous said...

Is it possible to pass username and password in the command line ?

Something like this :

"C:\Program Files\QlikView\Qv.exe" /r /username/password "C:\Users\MarcoRizzo\Desktop\demo per Baritermo\apprendo per baritermo\Contabilita.QVW"

???

Please help me


-TB said...

There is no way that I know of to pass in the QlikView document username and password in a command line. (It would compromise the security if it were possible to put the username and password into a script or text file.)

You could try posting your question to QlikCommunity. Although I remember seeing similar questions on QlikCommunity forums, I don't remember anyone offering a solution.

Anonymous said...

Great article on using cmd line to run qvw file. used it to execute different layers of qvw in sequential order.

Anonymous said...

Hi,
trying to trigger macro on post reload event.it works on desktop but noton server reload(Known issue in QV).tried the same with cmd line reload ,still does not work, need help

Unknown said...

I had an issue where the qvw I was reloading included vbscript macros used in the script and these macros required system level access. The load was failing when running from a batch file using just /r , after much googling I figured that you need to add option /nosecurity. Thought I'd add it here as most google searches on the subject lead here and the article doesn't mention it. Thanks