Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!