Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
Earlier,I used Date(Floor(TimeStampField)) as Date for Date field and Time(Frac(TimeStampField)) as Time for Time field and it worked fine but now its not working. May be due to changed TimeStamp field structure but what function needs to be used here. Data file and qvw is attached.
Regards,
Taha
Try this:
Date:
LOAD Date(Floor(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as Date,
Time(Frac(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss'))) as Time,
[Created On]
FROM
Date.xls
(biff, embedded labels, table is Sheet1$);
Try this:
Date:
LOAD Date(Floor(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as Date,
Time(Frac(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss'))) as Time,
[Created On]
FROM
Date.xls
(biff, embedded labels, table is Sheet1$);
Thanks. It worked, however, its also giving the same result when using withour TimeStamp# function i.e. Time(Frac(Left([Created On], 19))) as Time. By the way, Whats the role of TimeStamp# function here ?
I noticed that all values im my TimeStamp field possess +00:00 at the right most side. Do you have any idea, what does that mean ?
TimeStamp#() function is used when QlikView is not able to understand you TimeStamp field as TimeStamp. We can use TimeStamp#(), Dat#(), Time#() function to help QlikView understand that our date format is little different then what we have specified at the top using SET function.
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';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';