Qlikview v8.5 vs v10

[#1]

I am on v8.5 and am in the process of converting to v10, but am having a problem with a load statement as it relates to dates

in v8.5, I have this load statement from an access database

DataTemp:

SQL SELECT

    DATE

FROM PRODUCTIONDATA;

Data1:

load

left(DATE,4) as bYear,

left(DATE,7) as bYearMon,

left(DATE,10) as bDate,

DATE AS DATE1,

Resident DataTemp;

bYear returns 2011

bYearMon returns 2011-08

bDate returns 2011-08-01

DATE1 returns 8/01/2011 12:00:00  AM

When I compile using v10, I get this

bYear returns 4077

bYearMon returns 40779

bDate returns 40779

DATE1 returns 8/24/2011 12:00:00  AM

I have this code at the top of my script

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Anyone know what setting needs to be set or adjusted?

Thanks

Roy

[swuehl]

Hi Roy,

40779 is the numerical representation of date/timestamp  8/24/2011 12:00:00  AM.

So it seems that your query to the database is returning that numerical value (which is ok, I think), because left(40779,4) resp. the other left functions will return also what you see.

When you say, Date1 returns ..., how do you test this? Looking at the data table?

1) It seems that in V8.5, you probably got a string back (or it is recognized as Date string in the left function somehow, or your date settings were different in V8.5 in combination with your database drivers?)

I haven't worked with 8.5 so far, so I can't test.

How have you compared V8.5 results with V10 results? Have you used the same machine / database drivers / database connection? Anything else different from your old settings except QV version?

2) If you want to correct your results in V10, you can use date/time functions instead of/in combination with string functions, like:

year(DATE) as bYear,

left(Date(DATE,'YYYY-MM-DD'),7) as bYearMon,

date(daystart(DATE),'YYYY-MM-DD') as bDate

Hope this helps,

Stefan

[#3]

Thanks for the response. I’ll try your suggestion.

I have both v8.5 and v10 loaded on my computer. I did a load in v8.5, then a minute later did a load in v10. So, same computer, etc

As for how I viewed it, I created a list box and viewed the data.

Roy