topic FileTime function to fetch last modified date of an excel in SharePoint in QlikView

FileTime function to fetch last modified date of an excel in SharePoint

apoorvasd — Tue, 22 May 2018 10:14:38 GMT

Hello Everyone,

I am trying to get the timestamp of last modified date of an excel file that is placed in SharePoint into QlikView using FileTime() function. Report reloads successfully but the field, "Last_Modified" has nothing in it when called. But for the file stored in a local drive, the function works well which returns the timestamp of last modification of the file. Script is as shown below.

Fact:

LOAD

[PRJ-No.],

' ' as Status,

' ' as Level,

'CAPEX' as Type,

'Hyperion' as Source,

FileTime() as Last_Modified,

'DOLLARS' as currency,

//FileName() as FileName,

//' ' as Project_Plan_Total,

// ' ' as Project_Actual_Total,

' ' as Plant,

[AOP

2018] as AOP_2018,

[AOP

2019] as AOP_2019

FROM

[http://abc.com/pro/ops/global/Project%20Finance%20Reports/XYZ/XYZ_AOP.xlsx]

(ooxml, embedded labels, table is AOP18_Overview, filters(

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Any help on this?

Thank you.

Re: FileTime function to fetch last modified date of an excel in SharePoint

petter — Tue, 22 May 2018 10:49:54 GMT

When you are using webfile (http and https) instead of a Windows file system file you will not be able to get file attributes (as that varies hugely between files retrieved this way I believe).

When it comes to SharePoint you could do a separate read of the information via the SharePoint list and get the file attributes that way altough it is cumbersome to do.

Re: FileTime function to fetch last modified date of an excel in SharePoint

apoorvasd — Tue, 22 May 2018 11:00:30 GMT

Hi Petter,

Thanks for your suggestion!

So there is no straight forward way to get this is it?

I will try out the method of getting last modified date from SharePoint list as you have mentioned above and see if this works. In the meantime I hope other experts have some suggestion too!

Thank you.