Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

40 Replies
Not applicable
Author

Try and in the select statement rename the word_day to work_day_temp. That should solve the issue that qlikview does not find the field.

Not applicable
Author

please note when i change the script a little 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,

and then reload I then get this error message:

but pressing 'OK' to this error message still lets the full script run and gives me the date in the format I require, if I can get rid of this error then I am good to go!

Not applicable
Author

John,

it is already but still returning the error "field not found work_day_temp"

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,



Not applicable
Author

Hmm that is very strange.

I don't have an answer straight away apart from try to look for an oracle function that converts your workday to the right format and skip the preceding load.

Not applicable
Author

the ORACLE return the field name in UPPER case, so in the load part of the script all the field must be written in UPPERCASE 😛

Not applicable
Author

haha that was simple, you learn something new every day.

Not applicable
Author

STILL NO JOY!! as simple as I can i will try to explain it....

Here is a screenshot of the result of running the script in QV9, look at the date format in the field "work_day_temp" laid out as 2007-12-01 00:00:00thats how I want it to appear.

now here is a screenshot of EXACTLY the same script, but ran in qv10

Notice now how the date format of the field "work_day_temp" now shows the format as garbage...

39455.00000

If i then change the syntax of the code to try and change that garbage to the correct format I then get the below result, with qlikview now adding hours and minutes from somewhere into my results which I dont want, I want the hours, minutes and seconds to show as zero like they do in QV9...ANY HELP!!

Not applicable
Author

In the SQL statement, try this:

to_char(WORK_DAY,'YYYY-MM-DD') AS WORK_DAY_TEMP,

this way you will drop the time in the sql statement already and the qlikview formating will not be able to pick it up.

Not applicable
Author

John,

I have tried this and yes it removes the hh:mm:ss but i need those to be displayed, but just zeroed out like they are in QV9

THANKS

lEE

Not applicable
Author

No worries,

you should be able to show the 00:00:00 easily by just changing the qlikview date formating for it.

Have a play and I'm sure you will get it to display that way.