2 Replies Latest reply: Jun 6, 2014 3:58 AM by James Larsson RSS

    Storing QVDs by date

      Hello guys,

       

      I try to store source data which are many excel spreadsheets into separate QVD files based on year of their creation. Following should be achieved:

       

      1 QVD = 1 Year from all excel sheets

       

      I have following code:

       

      Sub LoadDistinctYears

      Years:

      Load Distinct

      [Year]

      from $(vPathXLS)\CR_*.xlsx // load all XLS 2013, 2014 and so on

      (ooxml, embedded labels, table is [INPUT ]);

       

      MinMaxYears:

      Load

      max ([Year]) as MaxYear,

      min ([Year]) as MinYear

      Resident Years;

      Drop table Years;

       

      ENDSUB;

       

      Call LoadDistinctYears;

       

      LET vMaxYear = FieldValue( 'MaxYear', 1 );

      LET vMinYear = FieldValue( 'MinYear', 2 );

       

      Sub LoadXLSData

      for i = 2013 to $(vMaxYear)

       

      CR_CORE_DATA_ALL:

      LOAD

           All columns

           FROM $(vPathXLS)\CR_*.xlsx (ooxml, embedded labels, table is [INPUT])  // loads CR_2013, 2014 and so on

                where [Year]=$(i);

      STORE CR_CORE_DATA_ALL into $(vPathQVDStore) CR_DATA_$(i).qvd;

       

      next i

       

      ENDSUB;

       

      Call LoadXLSData;

       

      What this currently does is storing data into QVDs year by year, but every year has duplicate data in it even also for year previous.

       

      So 2013.qvd has all data for year 2013 but 2014.qvd has data for 2013 and for 2014.

       

      I would really like to see your input on this one. I guess I am wrong in part - (for i = .. to )

       

      Thanks,

                James