topic Re: Date issue in QlikView

Date issue

Thu, 27 Feb 2014 21:42:40 GMT

Hi all.

I have two tables and one of them have a date field called "AnswerCreated" and the format for that field is "2014-02-04 19:42:22.130" and when I look at it in QV "41407,71546412".

I would like to use that field as a key to a Calendar but what is the best way to format that field?

See attached qvw.

Re: Date issue

Nicole-Smith — Thu, 27 Feb 2014 21:58:46 GMT

Settings > Document Properties > Number:

Re: Date issue

MK_QSL — Thu, 27 Feb 2014 21:59:38 GMT

Either use below in UI side

=Date(Date#(AnswerCreated,'YYYY-MM-DD hh:mm:ss'))

Or below in script side

Date(Date#(AnswerCreated,'YYYY-MM-DD hh:mm:ss')) as AnswerCreated

Re: Date issue

Fri, 28 Feb 2014 12:15:07 GMT

Yes, I would like to do it in the script but that doesn't work. The field AnswerCreated is empty, doesn't you need to take care about the last three numbers?

Re: Date issue

sunilkumarqv — Fri, 28 Feb 2014 12:21:40 GMT

open the script

Please find the attached give feed back hope thio helps you

Re: Date issue

manojkulkarni — Fri, 28 Feb 2014 12:24:34 GMT

use

Date(AnswerCreated,'YYYY-MM-DD') AS Date...

Re: Date issue

ashwanin — Fri, 28 Feb 2014 12:26:03 GMT

use

Date(AnswerCreated,'DD-MMM-YYYY') As AnswerCreated

in the script.

Re: Date issue

er_mohit — Fri, 28 Feb 2014 12:27:55 GMT

Try this in text object

=Date(Date# ('2014-02-04 19:42:22.130','YYYY-MM-DD hh:mm:ss.fff'),'YYYY-MM-DD hh:mm:ss' )

Re: Date issue

sunilkumarqv — Fri, 28 Feb 2014 12:32:08 GMT

try this into your textobject not required that of script Mohit.

=Timestamp#(Now(),'DD/MM/YYY hh:mm:ss')

Re: Date issue

PrashantSangle — Fri, 28 Feb 2014 12:32:17 GMT

Hi,

try this

timestamp(timestamp#(AnswerCreated,'YYYY-MM-DD hh:mm:ss')) as AnswerCreated

Re: Date issue

Anonymous — Fri, 28 Feb 2014 14:07:45 GMT

Add Date field based on AnswerCreated:

Answer:
LOAD
AnswerCreated,
date(floor(AnswerCreated)) as Date,
Metaparenttype
FROM D:\QV_Data\Admin\QVDs\MIS_Quiz.qvd (qvd);

You have dates without time in Calendar, but timestamps (dates with time) in Answer. Function floor() removes the time part. Function date() formats it as date as define in your "SET DateFormat", so it is the same as in Date in Calendar.

Regards,

Michael