topic Re: Pass variable from user to script? in QlikView

Pass variable from user to script?

Tue, 31 Jul 2012 18:43:34 GMT

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?

fosuzuki — Tue, 31 Jul 2012 19:10:22 GMT

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?

Tue, 31 Jul 2012 20:09:55 GMT

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?

fosuzuki — Tue, 31 Jul 2012 20:37:02 GMT

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 — Tue, 31 Jul 2012 23:01:56 GMT

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?

Wed, 01 Aug 2012 04:54:52 GMT

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?

Wed, 01 Aug 2012 12:59:27 GMT

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)

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?

Re: Pass variable from user to script?

Lee_Matthews — Wed, 01 Aug 2012 22:45:37 GMT

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.

Re: Pass variable from user to script?

fosuzuki — Thu, 02 Aug 2012 14:19:54 GMT

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

Re: Pass variable from user to script?

Thu, 02 Aug 2012 15:20:03 GMT

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

Re: Pass variable from user to script?

swuehl — Thu, 02 Aug 2012 15:44:42 GMT

Not sure if this helps, but there is actually a system function in QV to query the user for input:

Let vDate = Input('Enter Start Date (MM/DD/YYYY)' , 'Input required');

Re: Pass variable from user to script?

Thu, 02 Aug 2012 15:59:01 GMT

Stefan!

That's the answer - so simple! I knew there had to be an easier way to do it; I didn't realize this was how.

Thanks so much!

Re: Pass variable from user to script?

Thu, 02 Aug 2012 20:01:31 GMT

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!

Re: Pass variable from user to script?

Fri, 03 Aug 2012 08:43:14 GMT

Hi Swuehl,

Is some thing like this possible on the front end too? I am trying to work the following scenario.

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

Rahul Lakhina

Re: Pass variable from user to script?

Fri, 10 May 2013 13:52:26 GMT

Hi all,

I use input functin in qlikview scirpt to pass the variable run time while executing the script.

If suppose I want to reload the qvw using QlikView Publisher, how would I pass that variable. Kindly advise.

Thanks,

Surendra

Re: Pass variable from user to script?

nagarjuna005 — Tue, 08 Dec 2020 07:14:19 GMT

Hi Swuehl,

Your solution is working in my local desktop , but i want to do the same in QMC .

how to do it could u please explain.

Thanks for your help