topic Re: Date problem in QV10 working on previous version in QlikView

Date problem in QV10 working on previous version

Mon, 24 Oct 2011 14:59:05 GMT

Hi,

I have a script loading from an oracle table and data load is limited by start date and end date. This script has been (and still is) working perfectly fine on Qlikview 8.20 (yeah I know, old version ). When transferring this to the newer QV10 server, I get "ORA-01861: literal does not match format string" error. It is the very same script, and I can cope it from the newer server to the old one, and the script works perfect.

Any ideas what I can do to solve this?

Let StartDate = date('2010-01-01','YYYY-MM-DD');
Let EndDate = date(today(),'YYYY-MM-DD');

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE

WHERE("TRANSACTION_DATE">= '$(StartDate)'
AND "TRANSACTION_DATE"< '$(EndDate)' );

Robert

Re: Date problem in QV10 working on previous version

Miguel_Angel_Baeyens — Mon, 24 Oct 2011 15:05:28 GMT

Hi Robert,

My guess is that $(StartDate) is not being parsed properly. Add this line just below the LET lines, just to see if the date has the proper format

TRACE $(StartDate);
TRACE $(EndDate);

Let us know the result. You might need to change your date format. If the driver to connect to the Oracle database has not changed, is the only problem I'd see.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 06:44:58 GMT

Hi and thank you for your input.

The reslut looks like this:

Connected
2010-01-01
2011-10-25
--- Script Finished ---

I could also mention that I have tried several formats of StartDate and EndDate, for example 'YYYYMMDD', 'YYYY-MM-DD hh:mm:ss' etc etc. I have also tried Timestap instead of Date function.

When loading a few records from the table without any limitation (commenting the where clause), the TRANSACTION_DATE fied gives following format

2006-12-10 12:30:34.

Is there any way in Qlikview checking whether the field is date or varchar or similar, to find out if it could have a leading space or so? This is just a long shot, I have basically no idea how to solve this.

Robert

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 06:51:34 GMT

hi try with date#(...) for both variables

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 07:08:30 GMT

Hi,

I've tried date#() with several formats as well, still the same error message

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 07:18:10 GMT

try this

Let StartDate = date('2010-01-01','YYYY-MM-DD');
Let EndDate = date(today(),'YYYY-MM-DD');

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE

WHERE("TRANSACTION_DATE">= '(StartDate)'
AND "TRANSACTION_DATE"< '(EndDate)' );

Date problem in QV10 working on previous version

Tue, 25 Oct 2011 07:34:54 GMT

Hi,

the script now ended in "ORA-01858: a non-numeric character was found where a numeric was expected".

Robert

Date problem in QV10 working on previous version

Tue, 25 Oct 2011 07:37:56 GMT

what is the TRANSACTION_DATE format?

Date problem in QV10 working on previous version

Tue, 25 Oct 2011 07:47:28 GMT

Hi,

I can not access PL/SQL at the moment, and thus not check the data type of the field TRANSACTION_DATE right now. As mentioned above, if I load some records from the table without any limitation (commented where clause), I get the format of

2006-12-10 12:30:34

But this is just the Qlikview presented format. Is there any way to check the data type of the field via Qlikview?

Robert

Date problem in QV10 working on previous version

Tue, 25 Oct 2011 08:05:40 GMT

try this

tmpLoad:
LOAD
makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3)) as TRANSACTION_DATE

WHERE

makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3))>= makedate(2010,01,01)
AND

makedate(subfield(TRANSACTION_DATE,'-',1),subfield(TRANSACTION_DATE,'-',2),subfield(TRANSACTION_DATE,'-',3))< today();

SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE;

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 08:33:33 GMT

Hi Robert,

This Oracle issue is due to parsing error in your script. The StartDate and EndDate format type is not matching to Oracles. I am not sure why this is occuring in one version but not the other. If you generate log (document properties-> Generate Log file) in both the versions we can compare the SQLs they are passing to Oracle and find the difference.

Its always best to user SQL without custom formats. The following code will work if your QVS and Oracle are in the same timezone. If not incorporate the difference.

Let DateDiff = num(date(today(),'YYYY-MM-DD')-date('2010-01-01','YYYY-MM-DD'));

tmpLoad:
LOAD
"TRANSACTION_DATE";
SQL SELECT
"TRANSACTION_DATE"
FROM MY_TABLE
WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'
AND "TRANSACTION_DATE"< sysdate );

Hope this helps,

Kiran.

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 09:29:37 GMT

Hi,

these are the log files, first from the old server, second is from the new server

2011-10-25 11:21:27: 0015  Let StartDate = date('2010-01-01','YYYY-MM-DD')
2011-10-25 11:21:27: 0016  Let EndDate = date(today(),'YYYY-MM-DD')
2011-10-25 11:21:27: 0019  tmpLoad:
2011-10-25 11:21:27: 0020  LOAD 
2011-10-25 11:21:27: 0021  "TRANSACTION_DATE"
2011-10-25 11:21:27: 0022  SQL SELECT 
2011-10-25 11:21:27: 0023  "TRANSACTION_DATE"
2011-10-25 11:21:27: 0024  FROM MY_TABLE
2011-10-25 11:21:27: 0025  WHERE("TRANSACTION_DATE">= '2010-01-01'
2011-10-25 11:21:27: 0026  AND "TRANSACTION_DATE"< '2011-10-25'
2011-10-25 11:21:27: 0027  )
2011-10-25 11:21:30:        1 fields found: TRANSACTION_DATE, 10 lines fetched
2011-10-25 11:21:33:      Execution finished.
-----------------------------------------------------------------------------------------
2011-10-25 11:14:26: 0015  Let StartDate = date('2010-01-01','YYYY-MM-DD')
2011-10-25 11:14:26: 0016  Let EndDate = date(today(),'YYYY-MM-DD')
2011-10-25 11:14:26: 0020  tmpLoad:
2011-10-25 11:14:26: 0021  LOAD
2011-10-25 11:14:26: 0022  "TRANSACTION_DATE"
2011-10-25 11:14:26: 0023  SQL SELECT 
2011-10-25 11:14:26: 0024  "TRANSACTION_DATE"
2011-10-25 11:14:26: 0025  FROM MY_TABLE
2011-10-25 11:14:26: 0026  WHERE("TRANSACTION_DATE">= '2010-01-01'
2011-10-25 11:14:26: 0027  AND "TRANSACTION_DATE"< '2011-10-25')
2011-10-25 11:14:27:       Error: SQL##f - SqlState: S1000, ErrorCode: 1861, ErrorMsg: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string
2011-10-25 11:14:30:      Execution finished.

Howerver, the script you posted seems to work perfectly fine. I will incorporate that with the full load and match the results from the different servers.

Robert

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 09:36:36 GMT

ok let me know if it work

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 15:38:15 GMT

Hi,

when validating the full load from 2010 untill today, I get the exact same valuse on the old and the new server. Many thanks for the help!

The script used on the new server is now

tmpLoad:

Let DateDiff = num(date(today(),'YYYY-MM-DD')-date('2010-01-01','YYYY-MM-DD'));

tmpLoad:

LOAD

"TRANSACTION_DATE";

SQL SELECT

"TRANSACTION_DATE"

FROM MY_TABLE

WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'

AND "TRANSACTION_DATE"< sysdate );

The only "problem" that still persists is that sysdate refers to a time stamp. The applicatione is created so that a data load is extracting data to qvd-files every night with data between start date and end date. The data for current date is updated in another application via event driven load. On the previous server I was limiting the nightly data extract to midnight the day before, for instance 24th of October, and the data for 25th of October was loaded by the user at any time during the day via the event. If I load data until sysdate and then update with data for current date, I will eventually get double data for current date, both from QVD and from direct database load at event.

Is there any way to truncate sysdate to let the load stop at midnight?

Robert

Re: Date problem in QV10 working on previous version

Miguel_Angel_Baeyens — Tue, 25 Oct 2011 16:37:55 GMT

Hi Robert,

Try the following in your WHERE statement in the SQL to Oracle

WHERE TO_CHAR(TRANSACTION_DATE, 'YYYY-MM-DD') >= '2010-01-01'
AND TO_CHAR(TRANSACTION_DATE, 'YYYY-MM-DD') < '$(=Date(Today(), 'YYYY-MM-DD'))';

You can LET the Date(Today()) part in a variable if that suits you.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Date problem in QV10 working on previous version

Tue, 25 Oct 2011 18:06:40 GMT

Hi,

Use the following in your second condition instead of sysdate. It flags off data at midnight.

decode(to_char(sysdate,'HH24'),'01',0,sysdate)

Best Regards,

Kiran.

Re: Date problem in QV10 working on previous version

Mon, 07 Nov 2011 08:38:32 GMT

I'm sorry for bringing this up again, but I haven't had the time the past week.

My WHERE condition now says

WHERE("TRANSACTION_DATE">= sysdate-'$(DateDiff)'
AND "TRANSACTION_DATE"< decode(to_char(sysdate,'HH24'),'01',0,sysdate)

This now ends up in error message "ORA-00932: inconsistent datatypes: expected NUMBER got DATE"

Any ideas?

Robert

Re: Date problem in QV10 working on previous version

Mon, 07 Nov 2011 09:10:42 GMT

Can you try

"TRANSACTION_DATE"<=decode(to_char(sysdate,'HH24'),'01',TRUNC(sysdate)-1,TRUNC(sysdate))

Regards,

Kiran.

Re: Date problem in QV10 working on previous version

Mon, 07 Nov 2011 09:20:46 GMT

that seems to work like a charm, many thanks for your help!

Re: Date problem in QV10 working on previous version

Mon, 07 Nov 2011 09:27:37 GMT

Robert,

I didn't validate the logic so be vigilant while implementing.

Thanks,

Kiran.