pulling in only those excel files which belong to the entered date range

[#1]

I have many excel files (extracts) as the sources,each file belongs to a month.
Say..XYZ20080831,XYZ20080930,XYZ20081031,XYZ20081130 and so on.

I have to i/p fields for receiving a date range (i.e. from date and to dat)

Now, based on user date range selection how can I pull in only those files which belong to that date range?

I tried for..each..next ..as below..
--------------
for each File in filelist ('.\datasource\XYZ*.xsl')

load * from $(File);

next File
--------------
But the problem with this is,Each time when user wants reload the data,he has to manually put/move the required files in the folder it referes to (in this case, .\datasource)

Any help is much appreciataed.

Thanks.

[prieper]

Hi,

not quite clear, how you will get your user to make the input and do the reload - normally this should not be the task of users,

anyhow, provided you have the two variables dt_DateFrom and dt_DateTo script might look like (just aircode - not tested)

for each File in filelist ('.\datasource\XYZ*.xsl')

IF Date#(mid($(File), Len($(File)) - 12, 😎 , 'YYYYMMDD') > dt_DateFrom AND Date#(mid($(File), Len($(File)) - 12, 😎 , 'YYYYMMDD') < dt_DateTo THEN

load * from $(File);

END IF

next File

Idea is to read the timestamp in the files as a date and to compare it with your date-range

HTH

Peter

[#3]

Thanks Peter ! ..I think it will work.

[#4]

Hi Raj,

An the two variables can be changed through input boxes, i have done that and successfully works, let me know if you need the code.

Regards,

[prieper]

clear, but normally users cannot rerun the script. Maintenance of the input-boxes then still is with the Admin - isn't it?

Peter

[#6]

Hi Peter,

You set up input boxes and they do the same as changing values of variables (settings-variable overview), the user won't need to change the script and the user should be able to run the "Reload" option. Make sure that in Settings-documents Properties-Security the Reload option is ticked on.

It should work, let me know if any problems and I will upload a qlikview sample doc.

Regards.

Federico.

[prieper]

Hi Federico,

fully agree - there are multiple ways, but normal users cannot reload a file, they must have "admin"-rights and need to open the application (and save it probably thereafter).

For admins you may also load a bit more comfortable a kind of shadow calendar and then just pick the dates ...

Peter

[#8]

Hi Peter,

The reports I have designed for my company allow the users to reload the data (although they cannot save the information).

Interesting subject though how there are many ways of achiving the same result, suppose it depends on what is more comfortable for both ends.

I make the users call reports through asp front end and I added buttons that do a "run as" /r and then they can open the reports, but as I said, it depends on each other's taste.

Thanks for all your input Peter.

Federico.