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

[apoorvasd]

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.

[petter]

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.

[apoorvasd]

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.