topic Allowing the user to specify the date for SQL statement WHERE clause in QlikView

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

haymarketpaul — Wed, 12 Oct 2011 10:31:28 GMT

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;

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

richard_chilvers — Wed, 12 Oct 2011 11:01:27 GMT

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

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

Wed, 12 Oct 2011 12:31:05 GMT

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

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

haymarketpaul — Wed, 12 Oct 2011 12:54:36 GMT

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

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

swuehl — Wed, 12 Oct 2011 13:12:34 GMT

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

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

haymarketpaul — Wed, 12 Oct 2011 14:00:09 GMT

Fantastic - that's exactly what i need - Thankyou