How do I replace Excel file added with a new file with different name, without losing my dashboard?

[qlikbbguser]

Hello all,

My data is stored in Excel file - I uploaded first version of my Excel file to Qlik and used the data in that file to create a dashboard.  Let's say the file name is [MyExceldata.Sep.xlsx].

Say comes next month I have my refreshed data stored in another Excel file, named [MyExceldata.Oct.xlsx]. Can I upload/attach this new file to Data Manager and replace the Sep file? I tried to delete the Sep file and all charts and vizs are gone after I did so. or does the Excel file's name have to be the same every time I want to refresh/replace it?

Thanks.

[piotr-bratek]

Hi,

We need to make some assumptions. There is no direct way to replace the file in an example you have shared, so if we focus on the way we can solve it, I would think about the following steps:

1. Assumption 1: keep all files in the same folder: Sept, Oct and upcoming ones
2. You always need the latest file – we can’t rely on name - as ‘Sept’ / ‘Oct’ do not follow time order
3. Therefore, you can start with loading a table including two columns
   1. FileName         - FileName() as FileName
   2. FileTime            - FileTime () as FileTime – returning a timestamp in UTC format of the last modification of a specified file

FROM MyExceldata* (of course including path elements: lib:\\, etc.

4. Based on that, you can filter the row with the latest file using either:
   1. FirstSortedValue() function
   2. Or by sorting the data by FileTime column and selecting FileName from the first row – using a peek() function

As a result, you get the latest file name.

Store it into a variable with LET function including other path elements in order to get sort of: = 'lib://…/Test_Path/MyExceldata.Oct.xlsx';

5. Finally, load your data from a dynamic path with a variable:
   1. FROM [$(vPathName)]