topic Re: Date Format Issue in QlikView

Date Format Issue

Thu, 06 Feb 2014 11:15:24 GMT

Hey,

I have date in the following format including time stamp.

29/09/2013 09:15

When I load this date field in qvw file it is represented in the following format

41487.4638888889

I want to separate time stamp and date in order to use only date as dimension.

any ideas??

Re: Date Format Issue

Thu, 06 Feb 2014 11:18:38 GMT

try this

date(date#(fieldname,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')

Re: Date Format Issue

Thu, 06 Feb 2014 11:20:55 GMT

try this

in script-

Date(Date#(datefieldname,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as datefield

example--

in text box

=Date(Date#('29/09/2013 09:15','DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')

Re: Date Format Issue

manojkulkarni — Thu, 06 Feb 2014 11:25:59 GMT

use Date() function, below is sample

Date(datefield,'DD-MM-YYYY') for date

Date(datefield,'hh:mm') for timestamp

Re: Date Format Issue

Sokkorn — Thu, 06 Feb 2014 11:26:14 GMT

Hi Hannan,

Floor(DateField) As Dates -> return 41487

See this and this for details.

Regards,

Sokkorn

Re: Date Format Issue

Thu, 06 Feb 2014 11:27:05 GMT

Vishwaranjan,

I want to remove the time stamp from the date preserving the actual date .. I dont want timestamp in my date.

Re: Date Format Issue

Thu, 06 Feb 2014 11:30:02 GMT

I have date like this in excel file

29/09/2013 09:15

and when excel file is Loaded in the qvw it shows

41487.4638888889

I want to remove time stamp from my date.

I have this '29/09/2013 09:15'

I want this '29/09/2013' in my qvw.

Re: Date Format Issue

er_mohit — Thu, 06 Feb 2014 11:30:04 GMT

Try this

date(date#(fieldname,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as Date,

time(time#(fieldname,'DD/MM/YYYY hh:mm'),'hh:mm') as Time

Re: Date Format Issue

Thu, 06 Feb 2014 11:32:09 GMT

try this

Date(Date#(datefieldname,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as datefield

Re: Date Format Issue

Sokkorn — Thu, 06 Feb 2014 11:32:30 GMT

Hi Hannan,

That very clear. This one should do your job

Date(Floor(DateField)) As Dates

Regards,

Sokkorn

Re: Date Format Issue

Thu, 06 Feb 2014 11:32:49 GMT

Tried it already but its not working.

Re: Date Format Issue

Thu, 06 Feb 2014 11:36:22 GMT

Its working but my number of entries have been reduced. Count of date is not the same after applying this.

Re: Date Format Issue

juleshartley — Thu, 06 Feb 2014 11:38:14 GMT

What is the result?

Re: Date Format Issue

Thu, 06 Feb 2014 11:42:50 GMT

try this

Aggr(nodistinct Date(Floor(DateField)), Date(Floor(DateField)))

*********************************************

Re: Date Format Issue

juleshartley — Thu, 06 Feb 2014 14:12:44 GMT

Surely you will have fewer dates... as the granularity has been decreased to day, without time?

Re: Date Format Issue

Sokkorn — Fri, 07 Feb 2014 01:17:35 GMT

What is your count expression?

Re: Date Format Issue

Anonymous — Fri, 07 Feb 2014 03:15:22 GMT

From the Help Manual:

DayStart(timestamp [, shift = 0 [, dayoffset = 0]])

Returns a value corresponding to a timestamp with the first millisecond of the day contained in timestamp. The default output format will be the TimestampFormat set in the script. Shift is an integer, where the value 0 indicates the day which contains date. Negative values in shift indicate preceding days and positive values indicate succeeding days. If you want to work with days not starting midnight, indicate an offset in fraction of a day in dayoffset, e.g 0.125 to denote 3am.

Examples:

daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'

daystart ( '2006-01-25 16:45', -1 ) returns '2006-01-24 00:00:00' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'

daystart ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25 12:00:00' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000'

Re: Date Format Issue

Fri, 07 Feb 2014 04:50:28 GMT

Hi,

Try using this

subfield(F1,' ',1) as date1

this is will just remove the timestamp. But your field should be date format.

DD/MM/YYYY hh:mm

And this will not reduce the count of dates.

HTH

-Shruti