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]

Hi

Try to encase your date in the dayname function and a date around that:

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

This will give you the date with an underlying time of 00:00:00 and format it as you want.

[#3]

Thanks for the prompt response and excuse me for my lack of knowledge.

Based on the syntax you have supplied, and adding my own field name in should it now then read

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

as this is not reloading?

[#4]

Hm that is strange, what error message do you get?

Might be my syntax is slightly off but all you would have to do is "reset" your timestamp to a datestamp and make sure you show the time.

Try using only:

dayname(workday) as workday,

(this will format the date to the preset default for your application and will have the underlying 00:00:00)

[#5]

if I change the line to read:

dayname(work_day) work_day,

I get the error :

SQL error: ORA-00904: "DAYNAME": invalid identifier

Script line: SELECT CALENDAR_ID, to_char(work_day,'dd/mm/yyyy') Work_Day_Date, dayname(work_day) work_day, WORKING_TIME, WORKING_PERIODS, DAY_TYPE, to_char(WORK_DAY,'IW') WEEK_NO, to_char(WORK_DAY,'YYYY') YEAR, to_char(WORK_DAY,'MON') MONTH_NAME, to_char(WORK_DAY,'MM') MONTH,

[#6]

And if I change the syntax to read:

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

I get the error:

SQL error: ORA-00936: missing expression

Script line: SELECT CALENDAR_ID, to_char(work_day,'dd/mm/yyyy') Work_Day_Date, date(dayname(work_day),'YYYY-MM-DD hh:mm:ss') work_day, WORKING_TIME, WORKING_PERIODS, DAY_TYPE, to_char(WORK_DAY,'IW') WEEK_NO, to_char(WORK_DAY,'YYYY') YEAR, to_char(WORK_DAY,'MON') MONTH_NAME, to_char(WORK_DAY,'MM') MONTH,

[#7]

Ah ok, that is because you are doing a SQL select statement. You can't use qlikview functions within a SQL statement so will either have to use SQL functions or make a preceding load with the Qlikview function.

Can you post the code and I can see if I can sort it for you?

[#8]

Thanks

Yes we use one qvw to generate all our individual qvd files that the users then use to create their apps.

Here is the code we use to create our Calender, the stars obviously hiding specifc company data fields. All the fields listed pull through fine except for the "work_day" field

hope you can help!

Overview_Generated_Calendar_:

SELECT CALENDAR_ID,

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

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

Work_Time_Calendar_API.Get_Week_Day(WORK_DAY) WEEK_DAY,

WORKING_TIME,

WORKING_PERIODS,

DAY_TYPE,

to_char(WORK_DAY,'IW') WEEK_NO,

to_char(WORK_DAY,'YYYY') YEAR,

to_char(WORK_DAY,'MON') MONTH_NAME,

to_char(WORK_DAY,'MM') MONTH,

accounting_period_api.get_accounting_period(WORK_DAY) ACCOUNTING_PERIOD,

accounting_period_api.get_accounting_year(WORK_DAY) ACCOUNTING_YEAR

FROM WORK_TIME_COUNTER;

[#9]

Ah yes that makes sense.

I would to the following as it gives clean code, you could also convert the workday to the right format in the select statements with a dateformat function depending on what database you are running but as the calender seems small the benefits are small.

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,

Work_Time_Calendar_API.Get_Week_Day(WORK_DAY) WEEK_DAY,

WORKING_TIME,

WORKING_PERIODS,

DAY_TYPE,

to_char(WORK_DAY,'IW') WEEK_NO,

to_char(WORK_DAY,'YYYY') YEAR,

to_char(WORK_DAY,'MON') MONTH_NAME,

to_char(WORK_DAY,'MM') MONTH,

accounting_period_api.get_accounting_period(WORK_DAY) ACCOUNTING_PERIOD,

accounting_period_api.get_accounting_year(WORK_DAY) ACCOUNTING_YEAR

FROM WORK_TIME_COUNTER;

[#10]

Thanks...I just get this now when reloading...

Field not found - <work_day_temp>