Excel files - and creating trend graph with Qlik Sense?

[#1]

Hi All,

I get a excel file every day with a dataset (format always consistent)

Is it possible to fetch that file thru an static "URL" or network link that will return the daily excelfile. (tried it and it works).

However

The manual process is like this today; To copy-paste the data from the file into a second sheet. where I do a pivot table

every day - after load and refresh - the pivot results are manually copied into columns for that specific day, and that data is then used to plot the trend.

I would like to use Qlik to automate this and aggregate the data, plus ofc. give the possibility to filter and look into details

I think I need to solve two things:

1. Load the excelfile and add a datestamp to every row.

2. Then append the "temp"- data to the concatenated one within the Qlik application

and - bonus question - How to control a day's data is not loaded twice?

[quriouss]

Let's say you have an excel file with three columns: Customer, Product, Price and every day it is saved as "Sales_YYYY-MM-DD.xlsx"

The way I would approach it is

1. Load first Excel file
2. Save that into a QVD file

Then, on subsequent days;

1. Load most recent Excel file
2. Load data from previous QVD file (QlikSense will merge the data).
3. Save the QVD file

I'm writing this script from memory (untested) but it would look something like;

Script 1 (only need to run this once. Let's assume you start on Jan 1st)

Sales:

LOAD      {   Customer,

                    Product,

                    Price

                }

FROM [lib://Sales Data Files/Sales_2015-01-01.xlsx] (xls);

Store * from Sales into  'lib://Sales Data Files/Sales_Master.qvd';

Script 2 (to be run every day);

vTODAY = Today()

Sales:

LOAD     {    Customer,

                    Product,

                    Price

               }

FROM [lib://Sales Data Files/Sales_$(vTODAY).xlsx] (xls);

LOAD { * }

FROM [ 'lib://Sales Data Files/Sales_Master.qvd'] (qvd);

Store * from Sales into  'lib://Sales Data Files/Sales_Master.qvd';

You will probably need to make some corrections to that script, but I think the outline is there.

[#3]

Thank you very much !!