topic Re: Best way to parse timestamp (obtained from SQL) into separate date fields in QlikView

Best way to parse timestamp (obtained from SQL) into separate date fields

Mon, 24 Feb 2014 21:13:01 GMT

I have a timestamp field (startTime) that I am obtaining through the following SQL stmt in my script:

SQL SELECT DISTINCT event_id,

startTime,

FROM server.logs;

Then in my QlikView charts I am processing the dimension using a conditional:

= if (DateRangeUnit = 'Years', Year( startTime ),

if (DateRangeUnit = 'Months', MonthName( startTime ),

if (DateRangeUnit = 'Weeks', Date( WeekStart( startTime ) ),

if (DateRangeUnit = 'Days', DayName( startTime )))))

For efficiency, I am trying to do as many calculations ahead of time in my script, so trying to create new fields for Year, Months, Weeks, Days. So, essentially looking for something like this, but this isn't possible (obvious SQL errors):

SQL SELECT DISTINCT event_id,

startTime,

Year( startTime ) as year,

MonthName( startTime ) as month,

Date( WeekStart( startTime ) ) as week,

DayName( startTime ) as day,

FROM server.logs;

So, would I just grab the startTime as usual and them make a different table (or tables) that handles the unique dates?

Re: Best way to parse timestamp (obtained from SQL) into separate date fields

Anonymous — Mon, 24 Feb 2014 21:41:05 GMT

Jessica Ben wrote:

...

So, would I just grab the startTime as usual and them make a different table (or tables) that handles the unique dates?

Yes:

LOAD

event_id,

year(event_id) as Year,

month(event_id) as Month,

weekstart(event_id) as Week,

weekday(event_id) as weekday,

day(event_id) as Day,

date(floor(event_id)) as Date;

SQL SELECT

event_id,

startTime

FROM server.logs;

Re: Best way to parse timestamp (obtained from SQL) into separate date fields

hic — Mon, 24 Feb 2014 21:43:54 GMT

You should first load data using your SELECT statement:

ServerLogs:

Load *, Floor(startTime) as Date;

SQL SELECT DISTINCT event_id, startTime FROM server.logs;

The preceding Load is a QlikView syntax to load from the SELECT - or to transform the output of the SELECT. Then you load a Master Calendar using the above table as source:

Calendar:

Load distinct

Date,

Month(Date) as Month

Resident ServerLogs;

And you can add more fields in the Calendar: Week, Year, etc.

HIC