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...next
      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

      Directory

       

       

      for ii= 20100101 to 20100131

      hrs:



      set

       

       

      LOAD

       

       

      'DS'

       

      &$(ii) as Dataset,

      [Proj. def.]

       

      as Project_Nr,

      ...etc

      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

              Directory

               

               

              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

               

              hrs:

              set

               

               

              ErrorMode=0;

              LOAD

              $(ii)

               

               

              as DataSet,

              [Proj. def.]

               

              as Project_Nr,



               

               

               

               



               

              [WBS element]

               

              as WBS,

               

              [Activity desc.]

               

              as Activity,

               

              Work

               

              as Plan_hrs,

              [Work (A)]

               

              as Actual_hrs,

              RemainWrk

               

              as Remain_hrs

               

              FROM

               

               

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

               

              where

               

               

              Work+[Work (A)] > 1;

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