topic Dynamically load the qvds upon certain date selection in QlikView

Dynamically load the qvds upon certain date selection

Mon, 04 Jun 2012 18:45:52 GMT

Hi All,

Need help with this scenario from scripting gurus?

Have a situation where I have to develop a qlikview application which will dynamically load multiple qvds depending upon the date range selected and then start the reload process.

The qvd files are stored as:

hourly_03012012_01.qvd

hourly_03012012_02.qvd

hourly_04302012_23.qvd

hourly_04302012_24.qvd and so on for all 24 hours for each day for every month and year.

Depending upon the selection of the dates, for example Start Date: Mar 01 2012 and End date: Mar 12th 2012:

Have to loop through each qvds and pick the needed, load them one after the other, via load script in order and then start the reload process on the button click.

Any help would be greatly appreciated and would save tons of my development time as well.

Thanks,

Dynamically load the qvds upon certain date selection

jonathandienst — Mon, 04 Jun 2012 19:12:38 GMT

You could loop over the date range and wildcard load the qvds on each day. Something like this (where vMinDate and vMaxDate are dates selected by the user in an input box or calendar controls):

For zDate = vMinDate To vMaxDate

Set zFileMask = 'hourly_' & Date(zDate, 'MMDDYYYY') & '_*.qvd';

Data:

LOAD * From [$(zFileMask)] (qvd);

Hope that helps

Jonathan

Dynamically load the qvds upon certain date selection

adnan_rafiq — Mon, 04 Jun 2012 19:30:05 GMT

Jonathan has answered it almost. From what i have understood from Question I have one more thing to add.

if you are want to take input from user on presentation layer, You can define two variable with start and end date.

(can be calender objects with single value allowed).

once these values are passed at presentation layer can be used at script level.

Regards

Dynamically load the qvds upon certain date selection

Mon, 04 Jun 2012 19:50:00 GMT

Thanks all, it works! Created a calendar object to pass the parameter at script level.

Appreciate all the help!

Thanks,

Dynamically load the qvds upon certain date selection

Tue, 05 Jun 2012 13:41:50 GMT

Hi Jonathan,

Quick question to the posted discussion above.

My date object is the range of values which is Date(TempDate, 'YYYYMMDD') as QVDdates and shows me that I have picked 3 dates: 03012012, 03022012, 03032012.

How would I use these field called QVDdates in the for loop to get the results, could you please help.

Below is my screen shot for the selection:

Thanks and appreciate the help.

Dynamically load the qvds upon certain date selection

jonathandienst — Wed, 06 Jun 2012 20:38:57 GMT

No screenshot, but let's look at your question. If QVDdates is a field, then you will not be able to send those values directly to the load script.You will need to assign those values to a variable before starting the script.

You could create a button with a Set Variable action:

Variable: vSelections
Set value: =chr(39) & Concat(QVDdate, chr(39) & ',' & chr(39)) & chr(39)

This will create a comma separated, quoted list of you selection: Eg '03012012','03022012','03032012'

Now in the load script:

ForEach vQVDdate in $(vSelections)

...

Hope that helps

Jonathan

Dynamically load the qvds upon certain date selection

Wed, 06 Jun 2012 22:40:46 GMT

Thanks Jonathan, that worked and it was very helpful.

Now I have another scenario which I need help with.

I'm loading and storing daily and hourly data for everyday in seperate qvds and having seperate QVWs with respective data by hourly to get to it later as and when needed.

The main qlikview document has all the daily data for 2 years and granular data on hourly basis for current month only.

The scenario that I need help with is in the same qv document if a user picks certain MonthYear eg Jan_2012, upon a button click "Show Hourly Data" have to open a respective QlikView document which is saved and named as HourlyData_Jan_2012.qvw in the folder structure thus validating that it already has the current month in this qlikview document if they pick Jun_2012.

How should I persue with this as need to pass the variable or selection of Month_Year (Jan_2012) to open the qlikview document which has data pertaining to that month only.

Any suggestion or ideas how to persue with this would be greatly appreciated.

Thanks,

Re: Dynamically load the qvds upon certain date selection

Tue, 26 Aug 2014 11:49:30 GMT

Hi all,

I have a similar need to track changes based on daily QVD files.

I'm not able to retreive data into QV an receive the following error:

Cannot open file 'Q:\QVDocuments\QVD\'IPdata_'&Date(zDate)&'.qvd'' (cant find the files)

Data:

LOAD * From ['IPdata_'&Date(zDate)&'.qvd'] (qvd)

Here is my script based on above conversation:
...
FOR zDate= vMinDate To vMaxDate
SET zFileMask='IPdata_'&Date(zDate)&'.qvd';

Data:
LOAD * From [$(zFileMask)] (qvd);
Next

QVD's are named: IPdata_(YYYYMMDD) = IPdata_20140825 and are located in same folder with the QV.

I have also made vMinDate and vMaxDate calendar objects (with sigle value).

Sript error occurs depending on amount of days, so I asume that is not the problem..

Can you please help with this?

Thanks in advance,

Joni