topic Automate Report Start Date in SQL Using Variable? in QlikView

Automate Report Start Date in SQL Using Variable?

Thu, 28 Mar 2013 18:27:52 GMT

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?

Re: Automate Report Start Date in SQL Using Variable?

kaushiknsolanki — Thu, 28 Mar 2013 18:32:52 GMT

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)';

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

Regards,

Kaushik Solanki

Re: Automate Report Start Date in SQL Using Variable?

Thu, 28 Mar 2013 19:59:00 GMT

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?

kaushiknsolanki — Fri, 29 Mar 2013 05:48:09 GMT

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