Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've tried several master calenders , but none of them is working.
I have a feeling that is due to the datefield Im using.
My script looks like this:
//some script before
MaxMinDate:
load
min(Schichtdatum) as MinDate,
max(Schichtdatum) as MaxDate
resident Auftragsdaten_BDE;
let varMinDate = peek('MinDate');
let varMaxDate = peek('MaxDate');
TempCalendar:
Load
$(varMinDate) + RowNo() - 1 AS DateNumber,
Date($(varMinDate) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(varMinDate)+IterNo()-1 <= $(varMaxDate);
Calendar:
LOAD
date(TempDate, '$(DateFormat)') AS CalDate,
Weekday(TempDate) AS WeekDay,
Week(TempDate) AS Week,
Day(TempDate) AS Day,
Month(TempDate) AS Month,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Year(TempDate) AS Year,
Week(TempDate)&'-'&Year(TempDate) AS CWeekYear,
Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,
'Q' & ceil(month(TempDate) / 3)&'-'&Year(TempDate) AS QuarterYear
RESIDENT Temp_Calendar ORDER BY TempDate ASC;
DROP TABLE Temp_Calendar;
--------------------------------------------------------------------------------
The field description of the datefield is:
<FieldDescription>
<InternalNumber>12</InternalNumber>
<Name>Schichtdatum</Name>
<SrcTables>
<String>Result1</String>
</SrcTables>
<IsSystem>false</IsSystem>
<IsHidden>false</IsHidden>
<IsSemantic>false</IsSemantic>
<DistinctOnly>false</DistinctOnly>
<Cardinal>2469513</Cardinal>
<TotalCount>3273110</TotalCount>
<PossibleCount_OBSOLETE>-1</PossibleCount_OBSOLETE>
<HasInfo_OBSOLETE>false</HasInfo_OBSOLETE>
<IsLocked>false</IsLocked>
<AlwaysOneSelected>false</AlwaysOneSelected>
<AndMode>false</AndMode>
<IsNumeric>true</IsNumeric>
<Comment></Comment>
<Tags>
<String>$numeric</String>
<String>$timestamp</String>
</Tags>
</FieldDescription>
------------------------------------------------------------------------------------------------
Loading Log:
Auftragsdaten_BDE << Auftragsdaten_BDE 290'104 incl
MaxMinDate << Auftragsdaten_BDE 1 incl
TempCalendar << Autogenerate(1) 13'087'526 // the script doesnt run till the end - the condition for exiting the loop seems not to be reached
Do you see any errors in the script??
cheers //chesterluck
The problem was connected to the format of the datefield. here my solution:
num(date#(date(min(Schichtdatum))))
it returned 41174 as a number and could be used for the calculation!
Thanks to me
Some more info:
Ive just ran
MsgBox
(min(Schichtdatum))
MsgBox
(max(Schichtdatum))
with the result:
- min = 41174,741747685
- max = 41283,666238426
maybe it helps somehow
The problem was connected to the format of the datefield. here my solution:
num(date#(date(min(Schichtdatum))))
it returned 41174 as a number and could be used for the calculation!
Thanks to me