Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Dynamically load the qvds upon certain date selection

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,

DD

1 Solution

Accepted Solutions
jontydkpi
Not applicable

Dynamically load the qvds upon certain date selection

Hi

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);

     Next

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
jontydkpi
Not applicable

Dynamically load the qvds upon certain date selection

Hi

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);

     Next

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
adnan_rafiq
Not applicable

Dynamically load the qvds upon certain date selection

Hi

  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

Not applicable

Dynamically load the qvds upon certain date selection

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

Appreciate all the help!

Thanks,

DD

Not applicable

Dynamically load the qvds upon certain date selection

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.

DD

jontydkpi
Not applicable

Dynamically load the qvds upon certain date selection

DD

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)

     ...

     Next

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Dynamically load the qvds upon certain date selection

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,

DD

Not applicable

Re: Dynamically load the qvds upon certain date selection

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