Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

haymarketpaul
Contributor III

Allowing the user to specify the date for SQL statement WHERE clause

Hi All

I have the following SQL statement in my script which works fine with a hard coded date, but, is there a way to allow the user to provide the date for the WHERE clause?  Can i use a mapping table within the SQL so perhaps the user could update a mapping spreadsheet when they need to?

Any ideas welcome...

ODBC CONNECT TO QlikView (XUserId is TaDfPUJMJbXIWSZOfScKWQRMFTcII, XPassword is LSTEecYNJCaMDTJKFJWOFLC);

HBI_temp:

SELECT

    userEmail

    ,usertitle

    ,userfirstname

    ,userlastname

    ,userid

    ,dateadded

FROM

    tbl_Users

WHERE

    dateadded > '2011-10-02'            //<<<<<<<<<<< date i would like to make more dynamic <<<<<<<<<<<<<<<

;

HBI_Data:

LOAD

    userEmail           as Email

    ,usertitle             as Title

    ,userfirstname     as Firstname

    ,userlastname     as Lastname

    ,userid                as userid  

    ,dateadded          as From_Date

Resident

    HBI_temp;

DROP Table

    HBI_temp;

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Allowing the user to specify the date for SQL statement WHERE clause

Just adding a note: there is also a system function to query the user to input a value during script execution:

Input( str cue [, str caption] )

This function can be used in the script only and opens an input box that prompts the user for a value during the script execution. The parameters cue and caption are used as message and caption texts, respectively. The function returns the entered value.

The input box function returns NULL if the dialog is cancelled, closed or cannot be shown.

Example:

Load
Input('Enter value', 'Input box') as v,
Recno () as r
autogenerate 3;

Regards,

Stefan

5 Replies
richard_chilvers
Valued Contributor

Allowing the user to specify the date for SQL statement WHERE clause

Hi Paul

QV is very happy to extract data from Excel spreadsheets (you can use a wizard when writing the script). So this would be a good option. Create a spreasheet with a (single) date in it which your user can update. You may need a little validation in the script to make sure your user has asked for a 'sensible' date.

Regards

Not applicable

Re: Allowing the user to specify the date for SQL statement WHERE clause

Hi. I do not understand what the problem is? Do like this/

haymarketpaul
Contributor III

Re: Allowing the user to specify the date for SQL statement WHERE clause

Very useful thank you - perfect for what i need to do

MVP
MVP

Allowing the user to specify the date for SQL statement WHERE clause

Just adding a note: there is also a system function to query the user to input a value during script execution:

Input( str cue [, str caption] )

This function can be used in the script only and opens an input box that prompts the user for a value during the script execution. The parameters cue and caption are used as message and caption texts, respectively. The function returns the entered value.

The input box function returns NULL if the dialog is cancelled, closed or cannot be shown.

Example:

Load
Input('Enter value', 'Input box') as v,
Recno () as r
autogenerate 3;

Regards,

Stefan

haymarketpaul
Contributor III

Allowing the user to specify the date for SQL statement WHERE clause

Fantastic - that's exactly what i need - Thankyou