Download latest file

[ritvik4BI]

How can we automatically download the latest i.e the most recently modified file from SharePoint using Qlik Web Connector ?

[chrisbrain]

Hi - Is this for Qlik Web Connectors Standalone version?

The DownloadFile and GetFile tables should get the current/latest file.

[ritvik4BI]

Hi Chris.  

I'll explain the scenario to you. I have a folder here. Forecast folder. It has Jan,Feb,March, April Excel files. Now every month new files will be uploaded on SharePoint now after the new file come in old files are of no use to me. I want that my Qlik Web Connector should automatically fetch the latest modified file. i.e whenever the next file for the next month comes. It should automatically download it and create a connection in Qlik sense. How to do that?

[chrisbrain]

You should be able to use the ListFiles table to get all the files accumulating into that folder. This table has a last modified column so you should be able to sort the table on that, then take the first row to extract the file's UniqueId to then use in the DownloadFile or GetFile table.

[ritvik4BI]

Chrisbrain.... Yes the ListFiles table does contain the Last Modified column but how do we sort the tables on that so the last modified file's unique id appears on top of the table?????

[chrisbrain · Accepted solution]

Hi @ritvik4BI ,

Assuming you have already generated and pasted in the load script to load the ListFiles table you will likely have a table already called something like Office365Connector_ListFiles (you may have renamed this) containing you Jan, Feb, March files etc.

You can then use something like the following to do a resident load from this table with an 'order by' clause. Sorting it on descending modified time you can then just load the first row, peek the ID into a variable and then drop the table.

You can then use this vLatestFileId variable as an input to the DownloadFile or GetFile table to get the actual data.

LatestFileTemp:
first 1 load
ListFiles_UniqueId as ListFiles_UniqueId_Latest,
ListFiles_TimeLastModified as ListFiles_TimeLastModified_Latest
resident Office365Connector_ListFiles
order by ListFiles_TimeLastModified desc;

let vLatestFileId = peek('ListFiles_UniqueId_Latest', 0);

trace 'Most recent file ID is $(vLatestFileId)';

drop table LatestFileTemp;

[ritvik4BI]

@chrisbrain  Thanks and Regards.

[ritvik4BI]

@chrisbrain  I have a doubt.

Now i am loading the files in QlikView passing the vLatestFileId variable in the FROM statement.

ex Load A,B

FROM [https://localhost:......................................................$(vLatestFileId)]

Now i want that on any particular day say tomorrow if i get 3 new files instead of just one. Each file will have a new id and each file has not been loaded and i want to load all the new files which have come which happen to be 3 tomorrow but can be 5 or any number the day after how to that?  The above code works very well for fetching the latest modified file id but how can we fetch multiple files considering on any day more than one file comes in...

Thanks.

[stevedark]

HI @ritvik4BI 

You will need to do a small amount more work to get multiple files here.

Remove the first 1 from the code above, this will give you a list of all files.

You can then loop through the table with:

for iFile = 0 to NoOfRows('LatestFileTemp') -1

And then in the Peek you can refer to iFile:

let vLatestFileId = peek('ListFiles_UniqueId_Latest', iFile);

You will also need to peek out the ListFiles_TimeLastModified_Latest field and compare that to a variable that you set to now() at the end of each run. This can then be used to decide whether you load each file or not.

You will most likely want some kind of incremental load as well, so you get old records from QVD and new records from SharePoint. This article talks about a number of incremental load strategies:

https://www.quickintelligence.co.uk/qlikview-incremental-load/

Good luck!

Steve

[meenakshi_m]

hi Ritvik,

Can you please help on how to generate and past in load script to load list files table from sharepoint.

sharepoint is 2016 and using QlikSense. Connection to the share oint is established.