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.

Not applicable

Automate Report Start Date in SQL Using Variable?

I'm trying to automate the starting/cutoff point in my SQL using a reference to a variable created in my script. 

1. In my variables section, I have created a 'Let' variable that generates a date based on the report reload time.  I've called this variable vLastQtr.  It currently generates a date of 2/1/2012 (February 1, 2012) using the MakeDate function.  I have reviewed the variable and am satisfied that it generates the correct date.

2. In my SQL query, I am pulling data where the date >= '2/1/2012'.  In place of the '2/1/2012', I tried substituting $(vLastQtr), thinking that it would compare against the 2/1/2012 date generated by that variable.  The script errors out with a message of, "inconsistent datatypes: expected DATE got NUMBER."

Is it possible to substitute a variable in place of the date in SQL?  If so, how do I correct the data type consistency, so the SQL will recognize it properly?

3 Replies

Re: Automate Report Start Date in SQL Using Variable?

Hi,

     Try to assign the variable in this way.

     let vDate = '"'&Text(Date(MakeDate(2013,02,01),'DD-MMM-YYYY'))&'"';

     Sql Select * from abc where date >= '$(vDate)';

          or

     Sql Select * from abc where date >= $(vDate);

Regards,

Kaushik Solanki

Not applicable

Re: Automate Report Start Date in SQL Using Variable?

Thanks, Kaushik.  I tried your solution, but experienced the following issues:

If I use single quotes ( ' ') around the '$(vDate)' in my SQL, it fetches zero records.  The syntax check gives the appearance that it does not recognize it as a variable (not italicized or in gray like other variables.  Example: $(vLastQtrDate6).

If I do not use single quotes $(vDate) for my date criteria, the script returns an error that an expression is missing.  When I scroll to the date portion of the where clause in the SQL statement, the date is blank.

Any other thoughts?  I suspect that using a variable will work.  I just don't have the right date format.  I would send a QV sample, but it would be time-consuming to recreate.

Should I perhaps use single quotes in place of either of the double quotes in the let vDate formula?

Re: Automate Report Start Date in SQL Using Variable?

HI,

     When you use with Quotes it fecthed 0 record. Are you sure you have the data before the date specified in the variable?

Regards,

Kaushik Solanki

Community Browser