Multiple FileTime

[Anonymous]

I would like to use 2- FileTime functions to create:

File Upload Date = M/DD/YYY

File Upload Time=hh:mm

I currently have these variables set, but both the Upload Date and Time are getting formatted this way:

SET TimeFormat='hh:mm';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY hh:mm';

Is there a way to get 2 different results for these field?

Dan

[swuehl]

Maybe like

LOAD

     FileUploadTimeStamp,

     Date(floor(FileUploadTimeStamp), 'M/DD/YYYY')  as FileUploadDate,

     Time(frac(FileUploadTimeStamp), 'hh:mm') as FileUploadTime,

     ...

[Anonymous]

Thanks for the help, but it didn't work.

[swuehl]

Could you post your relevant script snippet?

[Anonymous]

swuehl,

Sorry for the delay, below is my script and a screen shot of what is happening. 

Script

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';

Time_Traffic_Light:

LOAD Time,

FileTime() as FileTime

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

Store Time_Traffic_Light into (qvd);

The Data Load was a simple Excel

Time

1:00:00

2:00:00

3:00:00

4:00:00

5:00:00

6:00:00

7:00:00

8:00:00

9:00:00

10:00:00

11:00:00

12:00:00

13:00:00

14:00:00

15:00:00

16:00:00

17:00:00

18:00:00

19:00:00

20:00:00

21:00:00

22:00:00

23:00:00

Here is a screen capture of what is happening:

Thanks!

Dan

[swuehl]

Filetime() will return a Timestamp, so it's formatted like the format set for TimestampFormat.

You should be able to reformat and split like shown above:

Time_Traffic_Light:

LOAD Time,

     FileTime() as FileTime

     Date(floor(FileTime()), 'M/DD/YYYY')  as FileUploadDate,

     Time(frac(FileTime()), 'hh:mm') as FileUploadTime

FROM

time.xlsx

(ooxml, embedded labels, table is Sheet1);

But what are you trying to achieve with this?

[Anonymous]

swuehl,

I have data source that contains just 1-days worth of data and I pull it from a SharePoint.  The data does not inclued any DATE & Time, so to compile a history I need to apply a date( which I am doing via FileTime.)  I then have to render a R/Y/G to the Time based on the process KPIs.

This worked and is providing a readable time!

Thanks for all the help!!

Dan

[Anonymous]

Swuehl,

Seems that the FileTime() function does not work when pulling from a SharePoint- is there a way to pull the MODIFIED date that is listed on the Sharepoint Document Library?  Or is there another way to get a "file load Date & Time" like the FileName() function was providing with the local data files?

In the QVD, all 3 fields are blank-

SharePoint Document Library

Dan