4 Replies Latest reply: Mar 7, 2012 7:29 AM by Paul Damen RSS

    Limiting number of months loaded

    Paul Damen

      Dear all,

       

      I have an excel file with different colums, 1 column says the month number (so 1,2,3 etc). And another column the year(2011, 2012). Now I want to restrict the load to only load the last 15 months (so today -15 months).

       

      Can anyone help me?

       

      Regards,

       

      Paul

        • Limiting number of months loaded
          Jonas Häggström

          Hi,

          Try using the following where clause:

           

          where makedate(YearColumn,MonthColumn) >= monthstart(addmonths(today(),-15))

           

          Good luck

          Jonas

            • Limiting number of months loaded
              Paul Damen

              And how do I incorporate this in the script?, script looks like this:

              SET

               

               

              ThousandSep ='.';
              SET DecimalSep =',';
              SET MoneyThousandSep='.';
              SET MoneyDecimalSep=',';
              SET MoneyFormat='€ #.##0,00;€ #.##0,00-';
              SET TimeFormat='h:mm:ss';
              SET DateFormat='YYYY.MMM.DD';
              SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
              SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';
              SET DayNames='ma;di;wo;do;vr;za;zo';
              SET PeriodFormat ='YYYY.MMM';

              SUB EXCEL
              Directory;
              LOAD Per as Maand,
              Jaar,                    
              Order,
              Bus.unit as BusinessUnit,
              Vakdiscipl. as Vakdiscipline,
              Omzet,
              Productgroep,
              KPV,
              Klantomschr.,
              Marge,
              Markt,
              Branche,
              [BBB 1] as BBB,
              [BBB 2] as Activiteiten,
              PtCtr as ProfitCenter,
              Postcode,
              Vestiging,
              Jaar&'.'&Per as Periode,
              [Regio Code],    
              If(isnull(Per) = 0, 'Q' & Ceil(Per/3)) as Kwartaal,
              Jaar&'.'& 'Q' & Ceil(Per/3) as KwartaalPeriode

              FROM
              [20120221 Dataset verkort v6.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);

              SET CurrentYear=Today('YYYY');
              SET PrevYear = Today('YYYY')-1;



              ENDSUB

               

              To translate: Jaar means Year, Per means month.

            • Limiting number of months loaded
              Jonas Häggström

              ...

              FROM
              [20120221 Dataset verkort v6.xlsx]
              (
              ooxml, embedded labels, table is Sheet1)

              where makedate(Jaar,Per) >= monthstart(addmonths(today(),-15));

              • Limiting number of months loaded
                Paul Damen

                I found it out!

                Working now thanks for the help!