Limiting number of months loaded

[pauldamen]

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

[#2 · Accepted solution]

Hi,

Try using the following where clause:

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

Good luck

Jonas

[pauldamen]

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.

[#4]

...

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

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

[pauldamen]

I found it out!

Working now thanks for the help!