Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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!!

Tags (1)
1 Solution

Accepted Solutions

Re: Date# returns correct number, Date() isn't parsing it

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
Partner
Partner

Date# returns correct number, Date() isn't parsing it

Try:

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

Hope this helps,

Jason

Not applicable

Date# returns correct number, Date() isn't parsing it

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

Partner
Partner

Date# returns correct number, Date() isn't parsing it

Can you post a sample of the data?

Not applicable

Re: Date# returns correct number, Date() isn't parsing it

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!

MVP
MVP

Date# returns correct number, Date() isn't parsing it

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.

Re: Date# returns correct number, Date() isn't parsing it

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

Partner
Partner

Re: Date# returns correct number, Date() isn't parsing it

You can use directly as

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

Not applicable

Re: Date# returns correct number, Date() isn't parsing it

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

MVP
MVP

Re: Date# returns correct number, Date() isn't parsing it

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.