Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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!
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,
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.
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 😛
haha that was simple, you learn something new every day.
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!!
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.
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
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.