2 Replies Latest reply: Apr 13, 2010 4:55 AM by Enrique Herranz RSS

    Daily excel file dataset loading to QlikView - How to set load ranges & Changed Data Capture

    Enrique Herranz

      I wondered if you could give me some help on the following : I need to load multiple excel sheets daily into Qview , so I plan to use the files naming convention as :

      YYYYMMDD-cost.xls ; YYYYMMDD-hrs.xls ; YYYYMMDD-revenue.xls ; ....etc

      Being YYYYMMDD ( YearMonthDate ). I would need to tell the Qlikview script to load all these files, but just from certain ranges , like :

      20100101 to 20100131 ( so, all days in January )
      20100201 to 20100228 ( all days in February )
      20100301 to 20100331 ( all days in March )...
      ......and so on , till December
      The problem is the only options I found in the help menu are two control sentences :
      For each ...next
      I can´t figure out how to write the script using any of those 2 sentences ( as the filenames will be arranged in these ranges , instead of a continuos list ) , I want Qlikview not to waste time trying to read files like 20100132, 20100133 ...etc ( they will not exist ). I know the set ErrorMode=0 will allow this ( the script will skip those without stopping ) ..but still the script will waste time on that. I would appreciate if you could give me some help on this or direct me to any existing forum post , whitepaper ...etc that could help me.
      Also does anybody know how to setup Qview to do Changed Data Capture prior to data loading ? Ideally I would need to setup the load script for these daily files in the best possible efficient way , so Qview does not waste time loading unchanged data from the previous data set ( a day older ). I would like Qview to load only new or changed data. I plan to trend data over time using these daily files loading.
      Appreciate any advice
      Thanks a lot

      //Multiple files load, file names : YYYYMMDD-hrs.xls




      for ii= 20100101 to 20100131










      &$(ii) as Dataset,

      [Proj. def.]


      as Project_Nr,


      ErrorMode=0; D:\00-OC Projects Dashboard\DataSets\hrs_CN47N;
        • Daily excel file dataset loading to QlikView - How to set load ranges & Changed Data Capture
          Michael Anthony

          The For Each ... Next function let QV cycle through a file list and action those that meet the criteria.

          If you define a variable, eg LoadYearMonth as 201003 etc and use this you can limit to finding files with your date range.

          Let LoadYearMonth = '201003'

          for each myFileName in filelist (..yourpath....&'\' & '$(LoadYearMonth)' & '*.xls)

          Load ... FROM '$(myFileName)'
          next File

          If your already loading you'll be able to fill in the correct formats etc for reading Excel. The reference manual also has doco on For Each.. Next

            • Daily excel file dataset loading to QlikView - How to set load ranges & Changed Data Capture
              Enrique Herranz

              Thanks for the advice Michael. In fact I haven´t tried it yet , as my first approach was to create a calendar table and then I simply used the For Each ...Next function . I copied all dates from the Calendar table and pasted them in the Load script . I am not sure this is an efficient way for Qview ...but for me it was the easiest to setup ( and so far it works ). Unfortunately I am still on the learning curve using Qview, and discover new things every day .

              My script is as follows :










              //Multiple files load, file naming : YYYYMMDD-hrs.xls




              D:\00-OC Projects Dashboard\DataSets;

              for each ii in 20090810,20090811,20090812,20090813,20090814,20090815,20090816,20090817,20090818,20090819,20090820, //....all Year dates from Calendar











              as DataSet,

              [Proj. def.]


              as Project_Nr,






              [WBS element]


              as WBS,


              [Activity desc.]


              as Activity,




              as Plan_hrs,

              [Work (A)]


              as Actual_hrs,



              as Remain_hrs





              [$(ii)-hrs.xls](biff, embedded labels, table is Sheet1$)





              Work+[Work (A)] > 1;

              // Work >0 OR [Work (A)]>0; ii