Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
Try:
Date(Date#(LastCallDate, 'DD/MM/YYYY h:mm:ss tt')),'DD/MM/YYYY') as CalendarDate
Hope this helps,
Jason
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
Can you post a sample of the data?
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!
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.
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
You can use directly as
Date(LastCallDate, 'DD/MM/YYYY h:mm:ss tt') as CalendarDate
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
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.