14 Replies Latest reply: May 10, 2013 9:52 AM by Surendra Masupalli RSS

    Pass variable from user to script?

      I have a script that has two variables, StartDate and EndDate. Right now, they are hard coded for testing, but obviously I want the users to be able to specify real dates in production.

       

      I guess I could go and create new variables for all the data variables in the script (like NewEquipAdd, NewEmpAdd, etc.) but right now I just specify StartDate and EndDate at the top of the script, and use constructions like "NewEquipAdd >= $(StartDate) AND NewEquipAdd <= $(EndDate)", so I'd like to keep that for simplicity.

       

      Is there a way for the user to:

      1 - Specify new start and end dates,

      2 - pass those values to my script variables, and

      3 - reload the script with the new variable values?

       

      Thanks for your help,

       

      Kevin

        • Re: Pass variable from user to script?
          Fernando Suzuki

          If the user will access the app via browser, the only way of doing this is by using EDX.

           

          You must create a macro with the EDX command, which will trigger the reload of the app in the SERVER, passing the variables and their values. After the reload is done in the server, the app will automatically refresh in the browser with the new data.

           

           

          Using EDX is kinda tricky, the above is just a summary of what happens. Check these topics:

          http://community.qlik.com/docs/DOC-2650

          http://community.qlik.com/thread/47353?tstart=0

           

           

          let me know if u need more details about this solution.

           

           

          Fernando

            • Re: Pass variable from user to script?

              That's way too complicated! All I want to do is grab start/stop dates from the user, and substitute them into the script. They are not necessarily going to be using a browser; in fact, for the first go-round, they certainly will not. The user will open the QV file, at which point I'd want a trigger to ask for start/stop dates, and then use those dates and reload the script.

               

              Alternately, is there any way to access and alter the script from outside of QV using VBA, for example?

                • Re: Pass variable from user to script?
                  Fernando Suzuki

                  The problem is that 'by default', the user can't trigger a reload in the server. This probably is because QlikView can't   multithread the reloads in the server, and imagine if there are several users trying to reload at the same time...

                   

                  You can eventually try something if the user is using QV Desktop. But I suppose they will access it with a browser, right?

                    • Re: Pass variable from user to script?
                      Lee Matthews

                      Kevin

                       

                      Using desktop and QVW files is a completely different paradigm to using QlikView server. In the latter instance each user shares the online QVW file in memory but things like variables are maintained for each user in the shared file. The load script does not have access to those variables. So you need to think of the long term solution if you eventually plan to deploy the solution to the server. Server is a multi-user, web-based, client-server environment - so it is a little complex by necessity. EDX is the framework to manage calls to the server to perform admin tasks in that environment. I am sure there is an example on community of how to use EDX to trigger a refresh.

                       

                      If you just want to do it in desktop, then it might work just having two input boxes attached to variables to hold your selected values. Then the user can click on the refresh button.

                       

                      Another approach that would work on desktop and also be adaptable to server is this:

                      - Create your input boxes on the UI linked to variables

                      - Write a macro to take those variables and store them in a database

                      - Write your SQL in the QlikView load script to reference the values stored in the DB

                      The user would enter the relevant values, click a button with anAction to run the macro and update the db. On the desktop a second Action could also trigger a refresh. When you deploy to the server you just need to replace the second Action with a EDX call to trigger the refresh.

                    • Re: Pass variable from user to script?

                      Kevin,

                       

                      Why do you want users to enter a date range if you can let them select date range on the dashboard instead? Like you'll be having two selection fields say Start Date and End Date.

                       

                      Asking for a specific date range prior to reload is quite not a good practice because it would take too much resources on the server if you're going to have multiple reloads.

                       

                      Regards,

                       

                      Bernardine

                        • Re: Pass variable from user to script?

                          Bernadine:

                           

                          Thank you for the suggestion; in fact, it was the first thing I tried. The problem is, the fields I'm loading are 'buckets' created by SQL commands within the script, and they don't have dates associated with them. e.g.:

                           

                          SELECT

                                UGC_ASPCode,
                                UGC_Desc,
                                (
                                SELECT COUNT(ENTC_ID)  FROM dbo.Entity_Table
                                WHERE ENTC_ASPCode=UGC_ASPCode
                                      AND ENTD_Created >= '$(StartDate)'
                                      AND ENTD_Created < DATEADD(day,1,'$(EndDate)')
                                ) AS TradePartnersCreated,

                           

                          So, "TradePartnersCreated" is just an integer without a date associated with it.

                           

                          Lee:

                           

                          I tried creating two input boxes for Start Date and End Date, but when I do a reload, it just overwrites them with the dates that are entered in the script. And that's regardless of whether I have the variables assigned in the script as:

                           

                          LET StartDate = addmonths(today(),-3,0)

                           

                          or

                           

                          SET EndDate = '6/30/2102'

                           

                          If you have any explicit instructions on how to make the "refresh" (I assume that means "reload"; please correct me if I'm wrong) use the newly entered values, I'm all ears!

                           

                          I'm not worrying about a server environment; we only have a few users with access to QV, and they would be running this from a desktop environment, accessing a SQL server only.

                           

                          Thanks to everyone for their help. I can't find any documentation about calling QV from a command line and passing variables to it that way; does such an animal exist?