What's wrong???

[Anonymous]

Hi all,

I try to load data from SQL.

LET dDate = date(MonthStart(today(),-13));

Tempo:

LOAD      

      Year(submitDateTime) as Year,

               Month(submitDateTime) as Month,

      Week(submitDateTime)& '-' & year(submitDateTime) as Week,

      'Q' & Ceil(Month(submitDateTime)/3)& '-' & year(submitDateTime) as Quarter,

      MonthName(Monthstart(submitDateTime)) as MYear,                                   // MMM YYYY

      QuarterName(QuarterStart(submitDateTime)) as QYYYY,*;

1:     

SQL SELECT * FROM dbo.aaa_690

WHERE date(submitDateTime)>=date('$(dDate)');

It should cut 13 months. But in this case it doesn't work. What do I miss?

Thanks in advanced,

[#2 · Accepted solution]

LET dDateday = day(date(MonthStart(today(),-13)));

LET dDatemonth = num(month(date(MonthStart(today(),-13))));

LET dDateyear = year(date(MonthStart(today(),-13)));

Tempo:

LOAD      

      Year(submitDateTime) as Year,

         Month(submitDateTime) as Month,

      Week(submitDateTime)& '-' & year(submitDateTime) as Week,

      'Q' & Ceil(Month(submitDateTime)/3)& '-' & year(submitDateTime) as Quarter,

      MonthName(Monthstart(submitDateTime)) as MYear,                                   // MMM YYYY

      QuarterName(QuarterStart(submitDateTime)) as QYYYY,*;

Test:     

SQL SELECT * FROM dbo.aaa_690

where

(CAST((  STR( ( YEAR(submitDateTime) ) ) + '/' +  STR( MONTH(submitDateTime) ) + '/' +  STR( DAY(submitDateTime) )  ) AS DATETIME) ) >= CAST((  STR( ( $(dDateyear) ) ) + '/' +  STR( ($(dDatemonth)) ) + '/' +  STR(($(dDateday)) )  ) AS DATETIME);

Try that, I may have had an extra ')' in there.

[Anonymous]

Thank you!!!! It works!!!!!!!!!

Did you use command from SQL? What's command CAST?

[#4]

Cast is a command for SQL 2005 that I had to figure out when I needed to do something similar. It basically is recasting (same thing as date function in qlikview syntax) the date time field as a date field. I do not know SQL syntax very well so I had to do a lot of digging to figure this one out.

In the future, if you ever need to do a simple where datefield>today() function, SQL syntax for Today() is GetDate().

So if you ever get the error: Today() is not a valid SQL command, use GetDate() instead

[Anonymous]

Thank you again.

[Oleg_Troyansky]

If you have access to the SQL Server database, try the same SQL as a direct query:

SQL SELECT * FROM dbo.aaa_690

  WHERE submitDateTime>=05/01/2011;

This is how your query looks to the SQL server when you replace the variable with it's date value. Based on your date format, the condition above may not fly.

Find the format of the date that leads to the correct result directly on the SQL Server, and then use that format in QlikView to format your variable accordingly.

[Anonymous]

Hi Oleg,

Thanks, I want to have only last 13 months.

I used my syntax for QVD file. But it's different for SQL.