Pass variable from user to script?

[#1]

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

[fosuzuki]

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

[#3]

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?

[fosuzuki]

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?

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

[#6]

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

[#7]

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?

[Lee_Matthews]

Hi Kevin

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.

[fosuzuki]

Hi Lee,

now I'm curious about your macro suggestion: how can one get the result from a SQL statement (select or procedure..) and insert those data into the QV data cloud? Can you please detail the "read that in" part?

Kind Regards,

Fernando

[#10]

Hi Kevin,

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!

Reagards

Rahul