topic Re: Distinct Timestamp not working in QlikView

Distinct Timestamp not working

Thu, 27 Mar 2014 16:52:45 GMT

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"

Re: Distinct Timestamp not working

Gysbert_Wassenaar — Thu, 27 Mar 2014 17:27:43 GMT

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.

Re: Distinct Timestamp not working

Thu, 27 Mar 2014 17:51:03 GMT

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;

Re: Distinct Timestamp not working

Gysbert_Wassenaar — Thu, 27 Mar 2014 18:22:52 GMT

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?

Re: Distinct Timestamp not working

Thu, 27 Mar 2014 18:27:58 GMT

I am wondering why my hours are repeating.

Re: Distinct Timestamp not working

Gysbert_Wassenaar — Thu, 27 Mar 2014 18:44:23 GMT

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)).