Distinct Timestamp not working

[#1]

I have a table of logs that contain a timestamp that I am rounding to the nearest hour:

AllKPIs:

Load timestamp( ceil( STARTTIME, 1/24 ) ) as AllKPITimestamp,

     etc...

Then I fill in that table so it includes every hour from start to end:

AllKPIMinMaxDate:

Load Min( AllKPITimestamp ) as AllKPIMinTime,

     Max( AllKPITimestamp ) as AllKPIMaxTime

     Resident AllKPIs;

Let vAllKPIMinDate = Peek('AllKPIMinTime',-1,'AllKPIMinMaxTime') - 1;

Let vAllKPIMaxDate = Peek('AllKPIMaxTime',-1,'AllKPIMinMaxTime');

FOR vHourStep = MakeTime(0) to MakeTime(23) STEP MakeTime(1)

JOIN (AllKPIs)

LOAD timestamp( $(vAllKPIMinDate) + IterNo() + $(vHourStep) ) as AllKPITimestamp

     AUTOGENERATE 1

     WHILE $(vAllKPIMinDate) + IterNo() <= $(vAllKPIMaxDate);

     NEXT vHourStep

Then I create a calendar object to handle the unique dates:    

AllKPICalendar:

Load DISTINCT AllKPITimestamp,

     DayName( AllKPITimestamp ) as AllKPIDay,

     Year( AllKPITimestamp ) as AllKPIYear,

     MonthName( AllKPITimestamp ) as AllKPIMonth,

     Date( WeekStart( AllKPITimestamp ), 'M/D/YYYY' ) as AllKPIWeek

     RESIDENT AllKPIs;

     //GROUP BY AllKPITimestamp;  

The problem is that when I chart by AllKPITimestamp it has duplicate timestamps (Day, Year, Month and Week are all distinct). I then tried to add the GROUP BY to my LOAD but that is giving me an error when I run Reload: "Aggregation expressions required by GROUP BY clause"

[Gysbert_Wassenaar]

Why do you want a Group by clause? You're not using an aggregation function like sum, max, min or avg, so there's nothing that needs to be grouped so an aggregated value can be calculated for each group.

[#3]

I don't need a GROUP BY clause, but I just don't understand why the DISTINCT isn't working to only add a timestamp to the Calendar table once.

AllKPICalendar:

Load DISTINCT AllKPITimestamp,

     DayName( AllKPITimestamp ) as AllKPIDay,

     Year( AllKPITimestamp ) as AllKPIYear,

     MonthName( AllKPITimestamp ) as AllKPIMonth,

     Date( WeekStart( AllKPITimestamp ), 'M/D/YYYY' ) as AllKPIWeek

     RESIDENT AllKPIs;

[Gysbert_Wassenaar]

Uhm... by rounding the log times up to the hour you create potentially 24 timestamps per day. And you wonder why you have more than one timestamp per day in your calendar table? Or am I misunderstanding you again?

[#5]

I am wondering why my hours are repeating.

[Gysbert_Wassenaar · Accepted solution]

Perhaps the issue described in this blog post is causing it: Rounding Errors

You could check that by adding that timestamp field to a listbox and format it as a number. If you see small differences in the digits far behind the decimal point you probably have your explanation. What you could try is using the floor function too. Something like timestamp(floor(ceil(STARTTIME, 1/24),0.000000001)).