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:
let me know if u need more details about this solution.
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?
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?
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.
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.
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 COUNT(ENTC_ID) FROM dbo.Entity_Table
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.
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)
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?
Yes, the script will always overwrite the variables if that is what it is coded to do. But you can actually create your variables in the doc properties, not the script, and then it wont overwrite them.
From the sound of what you say below though, I would write a macro to call SQL & run the proc, with it passing variables in the SQL command. If the proc can populate a sql table with the results you can then just read that in after the macro has run.
Why do you not try input boxes? I mean is there a perticular reason you want to do this at the script level?
Please see app attached!
Test4.qvw 411.5 K
Thanks for your help, but as I explained above, in my tables, there are no dates; there are only sums of counts made between start date and end date. So there are no date ranges in the tables to select from.
Stefan's answer does exactly what I want - asks the user for start and end dates, pops them into the script, and loads the data. Simple!
Is some thing like this possible on the front end too? I am trying to work the following scenario.
I have a hierarchy which comprise of 10 Front Line Managers and 15 to 18 Employees who work under each of them. I want the user to be able to select a Line Manager and/or some Employees under one or many Line Managers to form a test group. Once this group is established I want to use the excluded values to establish my control group?
Is this possible without changing the script?
Any help would be highly appreciated!
Thanks and Regards