topic Re: What's wrong??? in QlikView

What's wrong???

Anonymous — Mon, 07 May 2012 19:47:06 GMT

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

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,

What's wrong???

Mon, 07 May 2012 19:51:15 GMT

What version SQL server are you on?

What's wrong???

Anonymous — Mon, 07 May 2012 19:52:58 GMT

or 9 or 11

What's wrong???

Mon, 07 May 2012 19:54:42 GMT

Not 2005 or 2008? I know for my data I had to reconvert the dates in SQL 2005 code so that they would work together:

((CAST(( STR( ( YEAR(changedate) ) ) + '/' + STR( MONTH(changedate) ) + '/' + STR( DAY(changedate) ) ) AS DATETIME) ) >= CAST(( STR( ( $(LastExecTimeyear) ) ) + '/' + STR( ($(LastExecTimemonth)) ) + '/' + STR(($(LastExecTimeday)) ) ) AS DATETIME)

What's wrong???

Anonymous — Mon, 07 May 2012 19:58:06 GMT

I use my condition for other project.

CONCATENATE

LOAD DISTINCT * FROM $(vQVD)\Historical.qvd (QVD)

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

It work perfect. I try to SELECT use my condition. I see empty data.

Re: What's wrong???

Mon, 07 May 2012 20:00:03 GMT

I do know loading from a QVD will not be the same as loading SQL. In loading from a QVD I believe it uses Qlikview formulas in the Where section. When doing a Select from SQL it uses the servers formula logic.

It was the same for me at first before I converted it where I did not get any errors, just saw no data.

What's wrong???

Anonymous — Mon, 07 May 2012 20:10:44 GMT

I'll try to save in QVD, then load it again.

What's wrong???

Mon, 07 May 2012 20:13:33 GMT

Use SET instead of LET.

Stephen

Re: What's wrong???

Mon, 07 May 2012 20:18:57 GMT

Also in your WHERE statement remove the single quotes from '$(dDate)'

What's wrong???

Anonymous — Mon, 07 May 2012 20:25:10 GMT

I don't have problem when I load from QVD file. My syntax works.

When you load from SQL, it doesn't work.

Something wrong.

What's wrong???

Oleg_Troyansky — Mon, 07 May 2012 22:49:49 GMT

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...

What's wrong???

Anonymous — Tue, 08 May 2012 13:59:02 GMT

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,

What's wrong???

Tue, 08 May 2012 14:08:41 GMT

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

What's wrong???

Anonymous — Tue, 08 May 2012 14:11:45 GMT

It's time format.

I copied it from SQL

submitDateTime

2011-01-01 00:07:00

What's wrong???

Tue, 08 May 2012 14:14:02 GMT

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,

What's wrong???

Anonymous — Tue, 08 May 2012 14:21:37 GMT

Please find a capture.

Re: What's wrong???

Tue, 08 May 2012 14:28:31 GMT

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;

Re: What's wrong???

Anonymous — Tue, 08 May 2012 14:33:53 GMT

If remove the last ) before as Newtest.

It passed without error. Download all data.

Re: What's wrong???

Tue, 08 May 2012 14:38:25 GMT

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

Re: What's wrong???

Anonymous — Tue, 08 May 2012 14:41:56 GMT

I copied your syntax and I got an error.