7 Replies Latest reply: May 15, 2017 3:50 AM by Ankit Mishra RSS

    Automate process in Qlikview

    Anjali Ahlawat

      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.

        • Re: Automate process in Qlikview
          m w

          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);

            • Re: Automate process in Qlikview
              Peter Rieper

              .... 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

              • Re: Automate process in Qlikview
                Anjali Ahlawat

                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.

                  • Re: Automate process in Qlikview
                    Peter Rieper

                    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
                      Marcus Sommer

                      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
                    Peter Cammaert

                    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.

                    • Re: Automate process in Qlikview
                      Ankit Mishra

                      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