Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

anjali0108
Contributor II

Automate process in Qlikview

Hi All,

I am trying to automate the whole process of qlikview .

Just for the start I want to know how can I automate the connection in qlikview.

Is there anyway where I can make and test connections with database by passing userid and password without manually opening the qvw?

I want qlikview to create the connection and load data from mentioned tables automatically.

Please let me know if it is possible.

Thanks a lot.

7 Replies
mwoolf
Honored Contributor II

Re: Automate process in Qlikview

Is your connection via ODBC?

If so, make sure you enter the UserID and Password in the Connect to Data Source dialog.

Connect to DS.jpg

The resulting connection string will include the encrypted UserID and Password like this:

ODBC CONNECT TO PEGASYS (XUserId is bNfKBEJKfCUEXXBPfCVSWWVMdG, XPassword is IccEMZdNYLeEDYAGVRVA);

prieper
Honored Contributor II

Re: Automate process in Qlikview

.... and automation can be made easily with windows scheduler & and batchfile with a syntax like

FullPath\QV.exe /R FullPath\ApplicationToRefresh.qvw

you may scramble the password (as m w suggested) in script-editor

Peter

anjali0108
Contributor II

Re: Automate process in Qlikview

Hi,

Thanks for the revert but How come this is an automation?

That also needs to be done manually right.

I want to automate this without anyone opening the qvw and make a connection.

I want qlikview to automatically take values like database name, userid and pwrd for the below connection for different accounts:

ODBC CONNECT TO PEGASYS (XUserId is bNfKBEJKfCUEXXBPfCVSWWVMdG, XPassword is IccEMZdNYLeEDYAGVRVA);

I should have a generalized connection script wriiten in a way that whenever the whole qvw folder is placed in different different environments and reloaded by tasks,

that time it shud take userid and pwrd itself.

Hope you understand my requirement.

prieper
Honored Contributor II

Re: Automate process in Qlikview

Anjali,

have showed you the way, how to automate.

It will not work - or at least not that simple - if you place the qvw into different folders. For each environment you will have to create a new batch (or with advanced scripting trying to loop through various directories ...)

HTH Peter

Re: Automate process in Qlikview

I think you need to store all the user credentials within an excel- or an inline-table and then you could loop over them and store each credential within a variable which are used to create the connections. I mean something like this:

Credentials:

load * inline [

User, PW

U1, PW1

U2, PW2

];

for i = 0 to noofrows('Credentials') - 1

     let vUser = peek('User', $(i), 'Credentials');

     let vPW = peek('PW', $(i), 'Credentials');

     let vConn = 'ODBC CONNECT TO PEGASYS (XUserId is $(vUser), XPassword is $(vPW));'

     $(vConn)

     sleep 5000; // you might need to play with them to get a stable solution

     disconnect;

     sleep 5000;

next

This approach might be extended to an error-handling with the ERRORMODE feature and maybe creating a table within the loop to document which connections have been made and which of them were successful or not and why.

- Marcus

Re: Automate process in Qlikview

Also note that any working solution will stop funcitoning in a server environment. Why? Because the "reload" step is separated from the "display" step and an end-user has no control about which particular database is being accessed or what account will be used.

The only option there is to load all data for all users in your load script, and let the QVS decide on what to show to a particular user that opens the document. You can use Section Access and Data reduction to do that.

ankit777
Valued Contributor

Re: Automate process in Qlikview

Hi

You can create a config file where you put the data base connection string.

Include this in your Qlikview script.

Now every time you need to make a change in connection string, you change the config file and just run the QVW file.

You may also create certain varaibles in config file and use them in connection string in qlik script. Everytime you just need to change the variable values in the config file.

Hope that helps!

Thanks

Community Browser