topic Re: Script error with date field in QlikView

Script error with date field

QSense — Sun, 22 Jun 2014 14:06:07 GMT

Hello,

I have a script like below

SELECT /*+ parallel(8)*/

-- TRUNC (TO_DATE (TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')) TARIH,

-- SUBSTR (TIMESTAMP, 1, 4) AS YIL,

-- SUBSTR (TIMESTAMP, 6, 2) AS AY,

-- SUBSTR (TIMESTAMP, 9, 2) AS GUN,

-- SUBSTR (TIMESTAMP, 12, 2) AS SAAT,

-- SUBSTR (TIMESTAMP, 15, 2) AS DAKIKA,

REPLACE (REPLACE (UPPER (DOCUMENT), 'E:\DOCUMENTS\'),

'.QVW')

DOCUMENT,

TYPE,

USERS

FROM LOGS A

In this script When I comment date fields it works fine. But the whole script does not work. It gets QVX_UNEXPECTED_END_OF_DATA

error.

TIMESTAMP field is varchar data type and its format 'YYYY-MM-DD HH24:MI:SS .I change into date field.

a few times ago whole script was working.

İs there any one who has thougt about this problem ?

Thanks

Re: Script error with date field

fkeuroglian — Sun, 22 Jun 2014 14:12:32 GMT

Hi, can you put the qvw document as an example, to be easier to help yo to us?

this is only the select statement? the load statement where is?

thank you

Fernando

Re: Script error with date field

QSense — Sun, 22 Jun 2014 14:15:20 GMT

Hi,

I can not put it in here since data belongs to company.

There is no qvd file.It gets directly from sql select statements.

Thanks

Re: Script error with date field

fkeuroglian — Sun, 22 Jun 2014 14:20:16 GMT

your problem or error is in sql , not in qlikview

see this example

Date Functions in SQL Server and MySQL

http://www.sql-server-helper.com/tips/date-formats.aspx

Good luck

Fernando

Re: Script error with date field

QSense — Sun, 22 Jun 2014 14:54:25 GMT

whole script works in oracle .

Re: Script error with date field

fkeuroglian — Sun, 22 Jun 2014 14:57:51 GMT

TO_TIMESTAMP

Datetime Datatypes and Time Zone Support

Re: Script error with date field

Sun, 22 Jun 2014 15:23:50 GMT

Hi,

First load the date field from your data source and then use the date time function to extact the values for Year, month, day and time by using the Resident load.

Ex:

DirectRead:

Load Date#(TO_DATE, 'YYYY-DD-MM hh:mm:ss') AS TO_DATE;

Select TO_DATE from LOGS;

Resident Load:

Load Year(TO_DATE) AS YIL,

MONTH(TO_DATE) AS AY,

DAY(TO_DATE) AS GUN,

Hour(TO_DATE) AS SAAT,

Minute(TO_DATE) AS DAKKIKA

RESIDENT DirectRead;

Re: Script error with date field

maxgro — Sun, 22 Jun 2014 18:29:55 GMT

There are some post in the community with a similar problem (QVX_UNEXPECTED_END_OF_DATA), without answer, so I would start from this sql, no hint, no oracle function, just the fields

SELECT

TIMESTAMP,

DOCUMENT,

TYPE,

USERS

FROM LOGS A

;

ant if it works, add the function at the qlikview side (see also Venugopal suggestion)

LOAD

......

;

SELECT

TIMESTAMP,

DOCUMENT,

TYPE,

USERS

FROM LOGS A

;