topic SQL where condition in QlikView

SQL where condition

Tue, 03 Apr 2012 08:37:48 GMT

Hello,

I'm running below script.

LOAD Distinct
Date(floor(LotStartDateTime),'YYYY-MM-DD hh:mm:ss.fff') as TempMaxDateTime,
Date(WeekEnd(LotStartDateTime),'DD-MM-YYYY') as WeekEndDate
Resident LatestWeek;
Let vWeekEndDate=Peek('WeekEndDate',-1);
Temp1:
LOAD Date(Max(TempMaxDateTime),'DD-MM-YYYY') as MaxDateTime
Resident Temp;
Let vMaxDate=Peek('MaxDateTime',-1);
LOAD *,
LotStartDateTime AS TempLotStartDateTime
;
SQL SELECT *
FROM "Split Data".dbo."Week2"
Where
Day(LotStartDateTime) >= Day('$(vMaxDate)')
and
Day(LotStartDateTime) <= Day('$(vWeekEndDate)')
;

When i run the above query in qvw file, i'm getting the below error message as shown in second image.

In sql dates are like this

2011-06-08 00:10:12

2011-06-08 14:04:25

2011-06-09 11:00:04

SQL where condition

CELAMBARASAN — Tue, 03 Apr 2012 08:53:09 GMT

Hi,

Change this part

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

Day(LotStartDateTime) >= Day('$(vMaxDate)')

and

Day(LotStartDateTime) <= Day('$(vWeekEndDate)')

;

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

Day(LotStartDateTime) >= Day($(=Date(vMaxDate,'MM/DD/YYYY')))

and

Day(LotStartDateTime) <= Day($(=Date(vWeekEndDate,'MM/DD/YYYY'))

;

Celambarasan

Re: SQL where condition

Tue, 03 Apr 2012 09:01:02 GMT

If i change now i'm getting the error as

Re: SQL where condition

Tue, 03 Apr 2012 09:23:19 GMT

Sorry instead of Day i want to retrive the date only..

i'm poor in sql. please give a way to retrive the dates only

Re: SQL where condition

Tue, 03 Apr 2012 09:41:37 GMT

I tried below code also but it is not working...

Let vWeekEndDate=Peek('WeekEndDate',-1);

Let vWeekEndDateDB=Date('$(vWeekEndDate)','DD/MM/YYYY');

Let vMaxDate=Peek('MaxDateTime',-1);

Let vMaxDateDB=Date('$(vMaxDate)','DD/MM/YYYY');

LOAD *,

LotStartDateTime AS TempLotStartDateTime

;

SQL SELECT *

FROM "Split Data".dbo."Week2"

Where

CONVERT(nvarchar(10),LotStartDateTime,103) >= '$(vMaxDateDB)'

and

CONVERT(nvarchar(10),LotStartDateTime,103) <= '$(vWeekEndDateDB)'

qvw format are like this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: SQL where condition

sivarajs — Tue, 03 Apr 2012 09:48:56 GMT

Can u try this...

from ....

where date(LotStartDateTime)=date(to_date('$(vMaxDate)','yyyy-mm-dd') )

Sivaraj S

Re: SQL where condition

Tue, 03 Apr 2012 10:26:41 GMT

Still no use.

Re: SQL where condition

sivarajs — Tue, 03 Apr 2012 10:43:40 GMT

remove date() function just compare two dates like

where LotStartDateTime=to_date('$(vMaxDate)','yyyy-mm-dd')

Re: SQL where condition

Tue, 03 Apr 2012 11:17:41 GMT

No this error:

Re: SQL where condition

Tue, 03 Apr 2012 11:28:26 GMT

What i'm doing now is instead fo retriving less than weekend data. I'm trying to compare with particualar weeknumber. But i've seen that qlikview is reading the weeknumbers differently and sql reading weeknumbers differently.

For eg:

SELECT *

FROM [Split Data].[dbo].[Week2]

where

DATENAME(WEEK,LotStartDateTime) = DATENAME(WEEK,'2011-06-27 00:00:00')

here week number is 27

It retrives the dates from 2011-06-26 to 2011-07-02

But in qlikview if you use the below code

Load * from

table1.qvd

where

Week(LotStartDateTime) = 27

It retrives the dates from 2011-06-27 to 2011-07-03

here week number is 27

This creates a problem for my incremental process for weekwise qvd files. How can i control this.

Re: SQL where condition

Tue, 03 Apr 2012 12:42:17 GMT

I think, you should create string variable which contains all SQL + variables (dates) so SQL script is converted to SQL format and only then run the SQL script. Cause, SQL is using native script enterpritator.

SQL where condition

jonathandienst — Tue, 03 Apr 2012 13:36:53 GMT

Remember that all the command after SQL is passed to SQL Server and executed there, not by QV. The command must be in a form that SQL Server understands, and the Day function in SQL server does not accept a string parameter.

Use something like this in your original script:

Let vWeekendDate = Date(Peek('WeekEndDate',-1), 'YYYY.MM.DD');

... Day(LotStartDateTime) = Day(Convert(datetime, '#(vWeekendDate)', 102))

Check SQL Books Online for Convert or Cast.

Hope this helps

Jonathan

Re: SQL where condition

Wed, 04 Apr 2012 06:04:05 GMT

Dear Team,

I have one calendar which is coded in JavaScript. this i wanted to make it as QlikView extension object.

I am sharing the HTML, JavaScript and CSS file for reference.

Please guide / help in doing

Thanks in advance,

Thanks & Regards,

Venkatesh Salla

eTechnoforte FZC, PO Box No 120094,

SAIF Zone, Sharjah, UAE. Phone: +971 6 5578053

Email: <mailto:venkatesh@technoforte.co.in> venkatesh@technoforte.co.in

SQL where condition

jonathandienst — Sun, 08 Apr 2012 09:38:11 GMT

Hi V

I suggest that you create a new post for this request. It is not really the same thing as this post and you are more likely to get help.

Regards

Jonathan