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# returns correct number, Date() isn't parsing it

Hi all,

I have the following lines in my script:

Date(Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt')) as CalendarDate,

Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt')) as NumberDate,

LastCallDate

CalendarDate doesn't work

When I display it in a TableBox, it shows only a '-' in the CalendarDate field. The LastCallDate is shown correctly and NumberDate is also correct. I have no clue how to proceed.

I've included a picture that shows what I mean.

I hope that somebody can show me what's wrong with this, I've been trying different things now for more than an hour.

I have Qlikview 11, 64-bit version.

Thanks in advance!!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi,

It's likely to do with the date and time formats of your computer and the variables in the script for decimal separator. With your sample data, the following script works for me:

LOAD Timestamp(Num#(Timestamp#(LastCallDate, 'DD/MM/YYYY hh:mm:ss'), '#,#')) as CalendarDate,

Num(Num#(Timestamp#(LastCallDate, 'DD/MM/YYYY hh:mm:ss'), '#,#')) as NumberDate,

LastCallDate

FROM

example_date_data.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE Len(LastCallDate);

I prefer to use the functions Timestamp() instead of Date() because you have a complete timestamp (date and time) in the same field.

Hope that helps.

Miguel

View solution in original post

11 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Try:

Date(Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt')),'DD/MM/YYYY') as CalendarDate

Hope this helps,

Jason

Not applicable
Author

Hi Jason,

Thanks for trying to help out! This also doesn't work, it gives the same "-" as without specifying what the display of the date should be....

Really weird!

M

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Can you post a sample of the data?

Not applicable
Author

Hi Jason, here's the sample data.

My load-script is as follows:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD Date(Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt')) as CalendarDate,

Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt') as NumberDate,

LastCallDate

FROM

C:\Users\31077\Documents\example_date_data.xlsx

(ooxml, embedded labels);

A table box with this shows the same thing again (CalendarDate is not working, NumberDate and LastCallDate are).

Thanks!

swuehl
MVP
MVP

Your LastCallDate is already read in correctly as a timestamp (recognized by the excel data type).

So no need to parse the date using a date#() function.

Date(Daystart(LastCallDate)) as CalendarDate

should be enough.

Miguel_Angel_Baeyens

Hi,

It's likely to do with the date and time formats of your computer and the variables in the script for decimal separator. With your sample data, the following script works for me:

LOAD Timestamp(Num#(Timestamp#(LastCallDate, 'DD/MM/YYYY hh:mm:ss'), '#,#')) as CalendarDate,

Num(Num#(Timestamp#(LastCallDate, 'DD/MM/YYYY hh:mm:ss'), '#,#')) as NumberDate,

LastCallDate

FROM

example_date_data.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE Len(LastCallDate);

I prefer to use the functions Timestamp() instead of Date() because you have a complete timestamp (date and time) in the same field.

Hope that helps.

Miguel

jagannalla
Partner - Specialist III
Partner - Specialist III

You can use directly as

Date(LastCallDate, 'DD/MM/YYYY h:mm:ss tt') as CalendarDate

Not applicable
Author

Hi all,

Thanks for the quick help. I can just use date and timestamp directly.

I also figured out I needed to use Num#() if I want to use date() on a date#().

This is weird though, I haven't seen it before on the forums or in the documentation...

For future reference, this works (both CalendarDate1 and CalendarDate2 are correct):

LOAD Timestamp(Num#(Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt'))) as CalendarDate1,

Timestamp(LastCallDate) as CalendarDate2,

Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt') as NumberDate,

LastCallDate

swuehl
MVP
MVP

A

num(LastCallDate) as NumberDate

should be enough.

It's crucial that you take care of what the date/time functions do with your data.

Since we are coping with timestamps here, If you use

Date(LastCallDate) as CalendarDate,

this will just format your underlying timestamp's textual representation to a date representation, the numerical value will still hold the timestamp value.

Using this CalendarDate in a link to a master calendar will not result in matches between these two tables for most of the data.

Using this CalendarDate e.g. in a list box will show multiple entries with the same date text (if you have multiple timestamps for these days).

As a general rule, try to use 'Timestamp' as a part of your fieldname when you are handling timestamps, and 'Date' if you handle dates (with no time part).

To convert a timestamp to a date, use daystart() function.