Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
i got the Problem that my Calender only shows the Field StichtagJahrMonat till 2018-11 but i need it to show me also the StichtagJahrMonat 2018-12 (so the current Month)
It has the Data till today in it in the Field: #Buchungsdatum (since Dates of today)
My Calender looks like this:
// aus den (Hardware-) Bewegungen ermitteln wir das minimale und maximale Datum für einen Stichtagskalender
tmpMinMaxDatum:
load
min(#Buchungsdatum) as minDatum,
max(#Buchungsdatum) as maxDatum
Resident
Movements
where
istHardware = 1
;
// auslesen von min- und max-Datum
let vMinDatum = Peek('minDatum', 0, 'tmpMinMaxDatum');
let vMaxDatum = Floor(today());
// Roh-Kalender erzeugen
tmpCalendar:
load
*,
floor(MonthEnd(%Date)) as tmpMonthEnd
;
load
recno() + $(vMinDatum) - 1 as %Date
AutoGenerate $(vMaxDatum)-$(vMinDatum)+1
;
// Kalender finalisieren:
// wir behalten nur den jeweils letzten Tag eines Monats
// anreichern um weitere Felder wie Jahr und Monat
Calendar:
load
*,
Start & '\' & Ende as %StichtagStartEnde
;
load
$(vMinDatum) as Start, // wir setzen unser zuvor ermitteltes minimales Datum als Startpunkt für alle Stichtag-Intervalle
%Date,
%Date as Ende, // Das jeweilige Datum markiert immer das Ende des Stichtag-Intervalls
date(%Date) as Stichtag,
year(%Date) as Stichtagjahr,
month(%Date) as Stichtagmonat,
year(%Date) & month(%Date) as StichtagJahrMonat
Resident
tmpCalendar
where
%Date = tmpMonthEnd
;
// Intervall-Match, bei dem alle Buchungsdatumswerte in das jeweilige Sichtag-Intervall einsortiert werden (Start <= Buchungsdatum <= Ende)
tmpInterval:
IntervalMatch(%Buchungsdatum)
load
Start,
Ende
Resident
Calendar
;
// aus unserer temporären Linktabelle erzeugen wir eine neue, ebenfalls temporäre Linktabelle, aber mit veränderten Schlüsseln (kein synthetischer Schlüssel aus Start und Ende mehr)
tmpLnkCalendar:
load
%Buchungsdatum,
Start & '\' & Ende as %StichtagStartEnde,
Ende as #Stichtag
Resident
tmpInterval
;
drop table tmpInterval;
drop table tmpCalendar;
I guess the Problem lays here:
floor(MonthEnd(%Date)) as tmpMonthEnd
but i dont know how to change it...
Every help is appreciated.
Have a good day
Henry
Hello i changed it to :
tmpMinMaxDatum:
load
min(#Buchungsdatum) as minDatum,
max(#Buchungsdatum) as maxDatum
Resident
Movements
where
istHardware = 1
;
// auslesen von min- und max-Datum
let vMinDatum = Peek('minDatum', 0, 'tmpMinMaxDatum');
let vMaxDatum = Floor(today())+30;
// Roh-Kalender erzeugen
tmpCalendar:
load
*,
floor(MonthEnd(%Date)) as tmpMonthEnd
;
load
recno() + $(vMinDatum) - 1 as %Date
AutoGenerate $(vMaxDatum)-$(vMinDatum)+1
;
And now it worked fine for me
Thanks for the help anyway !
Hi
Maybe change your autogenerate statement and use this..
AutoGenerate 1 While $(vMinDatum) + IterNo() -1 <= $(vMaxDatum);
See if that works
Hi
If this is just dates, are you able to post an example. you could hard code the min and max dates so it doesn't need to read from the data.
Hi
In your script, the date field is called #Buchungsdatum but in the interval match you have it as %Buchungsdatum. is that correct?
When you use
floor(today())
in your script and run it, what does it give you? today's date? after the reload have a look at the vMaxDatum variable and see what it says.
Hello i changed it to :
tmpMinMaxDatum:
load
min(#Buchungsdatum) as minDatum,
max(#Buchungsdatum) as maxDatum
Resident
Movements
where
istHardware = 1
;
// auslesen von min- und max-Datum
let vMinDatum = Peek('minDatum', 0, 'tmpMinMaxDatum');
let vMaxDatum = Floor(today())+30;
// Roh-Kalender erzeugen
tmpCalendar:
load
*,
floor(MonthEnd(%Date)) as tmpMonthEnd
;
load
recno() + $(vMinDatum) - 1 as %Date
AutoGenerate $(vMaxDatum)-$(vMinDatum)+1
;
And now it worked fine for me
Thanks for the help anyway !