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,

[Oleg_Troyansky]

Loading from QVD vs. the SQL are two completely different things. QVD load is performed according to the QlikView syntax, while SQL SELECT is performed according to the SQL syntax. The QlikView function date() might not work exactly the same in SQL Server, depending on the specific version of your database.

try to simplify your condition like this and see if it works:

  WHERE submitDateTime>='$(dDate)';

or without the quotes:

  WHERE submitDateTime>=$(dDate);

One of those two should work, I think...

[Anonymous]

Hi Oleg,

Both of those case download all data.

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,*;

Test:     

SQL SELECT * FROM dbo.aaa_690

  WHERE submitDateTime>=$(dDate);

Thanks for your help,

[#4]

what is the base format of submitDateTime without changing it in the load section?

[Anonymous]

It's time format.

I copied it from SQL

submitDateTime

2011-01-01 00:07:00

[#6]

Can you try doing the below in the Select section for your script and let me know the format it returns, or if it returns an error?

CAST((  STR( ( YEAR(submitDateTime) ) ) + '/' +  STR( MONTH(submitDateTime) ) + '/' +  STR( DAY(submitDateTime) )  ) AS DATETIME) ) as Newtest,

[Anonymous]

Please find a capture.

[#8]

I meant to place it in the actual select portion like:

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,*;

Test:     

SQL SELECT *,

CAST((  STR( ( YEAR(submitDateTime) ) ) + '/' +  STR( MONTH(submitDateTime) ) + '/' +  STR( DAY(submitDateTime) )  ) AS DATETIME) ) as Newtest

FROM dbo.aaa_690;

[Anonymous]

If remove the last ) before as Newtest.

It passed without error. Download all data.

[#10]

Ok,

Try this script:

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

[Anonymous]

I copied your syntax and I got an error.