Script for a dashboard with history log

Mon, 15 Jun 2015 10:15:35 GMT

Hello Qlikviewers,

I have a script that loads an extract of active trades and 6 reports with reporting feedback regarding those trades. The script works fine and gives me the information that I need for my dashboard, although there may be ways to make the load script more efficient.

Now that I have a dashboard that works, I would like to extend the dashboard to a dashboard that also displays the historic data. Currently, the script loads the most recent files, resulting in an overview of the current status of the trades. However, now I want to graph historic data, displaying the development of certain trade characteristics over time. That means I would want to load the oldest files, until the newest files. The data can be displayed in one table, but then each load should have a report date time stamp, so that I can distinguish between reporting dates. I think I would need to write a loop to do such a thing, but I am not sure where to start.

I can upload my script if necessary but it is quite long, so perhaps it is easier to explain the concept of my script by summary:

load filedates //loading all filedates from the filenames on the server

//select the most recent filedate for which all files are available

incremental load:

load *

from $(vFile)

I do this for all files, and join them to make one large table that displays data from all reports for each position

Now I have one table that displays all the data for the latest report date.

Now I would have to write a loop that runs this script until all files available have been loaded, but for every date for which the script has loaded the data, it should also include a timestamp so that I can distinguish between current data and historic data.

I hope the concept is clear enough, I could do with just a little help of how I should get started on a loop that would serve my purpose.

Thanks in advance for any help.

Re: Script for a dashboard with history log

stigchel — Mon, 15 Jun 2015 10:39:31 GMT

It could be something like this, adjust to your needs (*.xls, *.qvd, and where the date is in your filename)

Set vConcatenate = ;

For each vFile in FileList('.\*.txt')

let vDate=mid('$(vFile)',5,8)

Data:

$(vConcatenate)

LOAD Field1 as FIELD1,

Field2 as FIELD2,

'$(vDate)' as DateStamp

FROM [$(vFile)];

Set vConcatenate = Concatenate ;

Next vFile

topic Script for a dashboard with history log in QlikView

Script for a dashboard with history log

Re: Script for a dashboard with history log