7 Replies Latest reply: Aug 26, 2014 7:49 AM by Joni Niemistö RSS

    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

        • Dynamically load the qvds upon certain date selection
          Jonathan Dienst

          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

          • Dynamically load the qvds upon certain date selection
            Adnan Rafiq

            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

            • 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

              • 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

                  • Dynamically load the qvds upon certain date selection
                    Jonathan Dienst

                    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

                  • 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

                    • 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