topic Re: Multiple FileTime in QlikView

Multiple FileTime

Anonymous — Tue, 19 May 2015 15:40:13 GMT

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

Re: Multiple FileTime

swuehl — Tue, 19 May 2015 15:43:37 GMT

Maybe like

LOAD

FileUploadTimeStamp,

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

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

...

Re: Multiple FileTime

Anonymous — Fri, 22 May 2015 02:11:19 GMT

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

Re: Multiple FileTime

swuehl — Fri, 22 May 2015 10:30:52 GMT

Could you post your relevant script snippet?

Re: Multiple FileTime

Anonymous — Tue, 26 May 2015 11:20:25 GMT

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

Re: Multiple FileTime

swuehl — Tue, 26 May 2015 11:30:34 GMT

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?

Re: Multiple FileTime

Anonymous — Tue, 26 May 2015 11:59:32 GMT

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

Re: Multiple FileTime

Anonymous — Tue, 26 May 2015 13:36:26 GMT

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