Date Format Calendar issue with Qlikview 10 not apparent in Qlikview 9

[#1]

We have an issue with a custom generated Calendar that now in Qlikview 10 is not pulling through the dates in the correct format.

I know there is a supposed fix for this in qlikview 10 sr1 but we are not in a position to upgrade at the moment so I was wondering if anyone could help re word our script to give us the date format we require.

In Qlikview 9 when pulling through a "work_day" field we would get the date in the following format

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

This format works perfect for us and is used to base calculations on for graphs etc.

Once upgraded to Qlikview 10 all our Graphs started to display the error "no data to display" even though the script had not been changed.

Digging further I found when pulling through the same "work_day" field in Qlikview 10 now gave me the date format as below

40379.000000

40378.000000

40234.000000

Which obviously is not right and is what was causing the issues with our graphs.

I have since re-written the script a little, so intsead of it just selecting the raw field, the select statement now says:

to_char(work_day,'yyyy-mm-dd' hh:mm:ss) Work_Day,

I reloaded the script thinking this would work and it has to a point, now the date field format is as below

2007-12-01 12:01:00

2007-12-01 12:02:00

2007-12-02 12:03:00

For some reason Qlikview 10 is now creating hours and minutes which should not be there, and were not present in QV9. This therefore is still causing the graphs data not to be displayed and all calculations on the date fields are not working at all.

Is there anyway (without upgrading for now) to change my script to show the date format as above but as it was in QV9 with the zerored out hours, minutes and seconds?. Ideally I want to rewrite my line of code to give me the date field in this format again.

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

If anyone could help it would be much appreciated. I have seen other posts mentioned the 'floor' function but with my lack of coding knowledge I am not sure how to implement this correctly into my existing code.

thanks

Lee Mackreath

[#2]

No worries,

in your select statement, I change it to say:

work_day as work_day_temp

This just to make it clear that we are changing it in the preceding load.

[#3]

I can get the script to now give me the required result, changing the syntax to this

Overview_Generated_Calendar_lee:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day;

SELECT CALENDAR_ID,

to_char(work_day,'dd/mm/yyyy') Work_Day_Date,

work_day,

I do however get an error when I reload which when I hit 'OK' to still lets the full script run, the error reads:

Syntax error, missing/misplaced FROM:

Overview_Generated_Calendar:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day

Overview_Generated_Calendar_lee:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day

any ideas?

[#4]

Yes seems like there is missing an 'AS'

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') AS work_day;

Might be the same for the SQL code.

[#5]

still not there...

i want to use the code you supplied.

Overview_Generated_Calendar:

LOAD *,

date(dayname(work_day_temp),'YYYY-MM-DD hh:mm:ss') AS work_day;

SELECT CALENDAR_ID,

to_char(work_day,'dd/mm/yyyy') Work_Day_Date,

work_day as work_day_temp,

but it just says field not found "work_day_temp" and does not go any further and does not reload the script.

If I change the code to say:

Overview_Generated_Calendar:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day;

SELECT CALENDAR_ID,

to_char(work_day,'dd/mm/yyyy') Work_Day_Date,

work_day,

then I get the message:

Syntax error, missing/misplaced FROM:

Overview_Generated_Calendar_lee:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day

Overview_Generated_Calendar_lee:

LOAD *,

date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day

but then I can click 'ok' and the script successfully runs...

so close!!!....any clues?

[#6]

Hmm try to take away the * and only load the work_day expression.

If this works, the problem is that work_day shows up twice.

You can then either replace the * with each field you want explicitly or try to rename the work_day somehow (no idea why it doesn't work with work_day_temp).

[#7]

Nope... Have taken out the *, so it just reads:

LOAD

date(dayname(work_day_temp),'YYYY-MM-DD hh:mm:ss') as work_day;

SELECT CALENDAR_ID,

to_char(work_day,'dd/mm/yyyy') Work_Day_Date,

work_day as work_day_temp,

and when I try to reload still get the error

Field not found - <work_day_temp>

[#8]

in your load statement it says

LOAD

date(dayname(work_day_temp),'YYYY-MM-DD hh:mm:ss') as work_day;

is the issue that qlikview cannot find this "work_day_temp" field because it does not actually exist in our oracle database?

the field "work_day" does and thats the one that I want to reformat....

[#9]

Hi lmackreath78,

can u give a snapshot with the error message dialog box and explain it briefly.

Regards,

[#10]

this

This is the error I recieve from qlikview when trying to run the below script

Overview_Generated_Calendar:

LOAD *,

date(dayname(work_day_temp),'YYYY-MM-DD hh:mm:ss') as work_day;

SELECT CALENDAR_ID,

to_char(work_day,'dd/mm/yyyy') Work_Day_Date,

work_day as work_day_temp,

[#11]

this is the main tab script to show how we connect to our database

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##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';

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##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';

LET DatabaseName = '*******************************';

LET ExportPath = '\\******************************************

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=*****;Data Source=$(DatabaseName);Extended Properties=""] (XPassword is *****************************);