Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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