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

[#1]

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?

[Anonymous]

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;

[hic · Accepted solution]

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