Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try using the following where clause:
where makedate(YearColumn,MonthColumn) >= monthstart(addmonths(today(),-15))
Good luck
Jonas
Hi,
Try using the following where clause:
where makedate(YearColumn,MonthColumn) >= monthstart(addmonths(today(),-15))
Good luck
Jonas
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.
...
FROM
[20120221 Dataset verkort v6.xlsx]
(ooxml, embedded labels, table is Sheet1)
where makedate(Jaar,Per) >= monthstart(addmonths(today(),-15));
I found it out!
Working now thanks for the help!