Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

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

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.

2 Replies
petter
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

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.