topic Re: Format date/time column to Year, Month, Day, Hour, Minutes in QlikView

Format date/time column to Year, Month, Day, Hour, Minutes

Anonymous — Tue, 31 Jul 2018 18:59:39 GMT

I have a SQL server data load that imports a column in the format

It is very difficult to manipulate this raw data for creating charts. The time is too granular. How can i format the time/date so that it is useable. Here is now it loads from my SQL database. I would like to parse the data out so that I have Year, Month, Day, Hour, and Minute Columns..

Re: Format date/time column to Year, Month, Day, Hour, Minutes

s_achraphe — Tue, 31 Jul 2018 21:46:09 GMT

Hello,

you can try something like this in your script qlikview/sense

Month(TimeSent,'MM') as MonthSent

Year(TimeSent) as YearSent

Or parse it in your SQL query with something like this :

EXTRACT(TimeSent, MONTH) as MonthSent

EXTRACT(TimeSent, YEAR) as YearSent

Hope it helps you

Re: Format date/time column to Year, Month, Day, Hour, Minutes

sunny_talwar — Wed, 01 Aug 2018 01:01:50 GMT

Use Preceding Load on top of your SQL load and then use date and time functions‌‌ to get date, month, year, monthyear... etc.... or you can also Creating A Master Calendar‌‌ to create this stuff

Re: Format date/time column to Year, Month, Day, Hour, Minutes

sibin_jacob — Wed, 01 Aug 2018 01:04:28 GMT

You can use a preceding load on top of the Sql Load,

You can use Date function to format the data or you can use Month, Year Function respectively.

Load *, date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'YYYY') as Year,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'MM') as Month,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'DD') as Day,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'hh') as Hour,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'mm') as Minute,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'ss') as Second

;

SQL Select Top 2000

"ID_Action"

Re: Format date/time column to Year, Month, Day, Hour, Minutes

MarioCenteno — Wed, 01 Aug 2018 02:49:40 GMT

Try

Master Calendar ir AutoCalendar

Re: Format date/time column to Year, Month, Day, Hour, Minutes

BalaBhaskar_Qlik — Wed, 01 Aug 2018 07:23:14 GMT

Try like this:

DailyTime:

Load

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt')) as TimeSent,

SQL

Select top 2000

"ID_ACTION",

"Time_Created",

"Time_Sent"

From Emp;

Calendar:

Load

Date(TimeSent) as TimeSent,

Day(Timesent) as Day,

Month(TimeSent) as Month,

Year(TimeSent) as Year,

Hour(Timesent) as Hour,

Minute(Timesent) as Minute;

Load

Date(MinDate + IterNo()-1) as TimeSent

while (MinDate + IterNo() - 1) <= Num(MaxDate);

Load Min(date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'))) as MinDate,

Max(date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'))) as MaxDate

Resident DailyTime;

Re: Format date/time column to Year, Month, Day, Hour, Minutes

Anonymous — Wed, 01 Aug 2018 16:47:01 GMT

This code runs.. But the new formatted data does not come thru properly. I do have some SET commands (company standard template) before my SQL code.

Here is the end result:

Here are my SET commands:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: Format date/time column to Year, Month, Day, Hour, Minutes

Anonymous — Wed, 01 Aug 2018 18:57:52 GMT

This code doesn't run in my Script..

Re: Format date/time column to Year, Month, Day, Hour, Minutes

Anonymous — Wed, 01 Aug 2018 19:57:02 GMT

I noticed I had a pre-built Calendar in my Script Template.. I replaced a generic DateID with my TimeSent field, but it still didn't like the format..

TempCal:
Load
MonthStart(today(),-RowNo()+1) as TimeSent
AutoGenerate(12);

Calendar:
Load
TimeSent,
TimeSent as DateKey,
Year(TimeSent) As Year,
Month(TimeSent) As Month,
'Q' & Ceil(Month(TimeSent)/3) As Quarter,
Ceil(Month(TimeSent)/3) As Qtr,
Date(MonthStart(TimeSent), 'YYYY-MM') As YearMonth
Resident TempCal;

Drop Table TempCal;

Let vToday = Today();

Ranges:
Load
DateRange,
Date(Evaluate(RangeStart)) as RangeStart,
Date(Evaluate(RangeEnd)) as RangeEnd
;
LOAD * INLINE [
DateRange|RangeStart|RangeEnd
Last Month|MonthStart(vToday,-1)|MonthEnd(vToday,-1)
Last Quarter|QuarterStart(vToday,-1)|QuarterEnd(vToday,-1)
Last Year|YearStart(vToday,-1)|YearEnd(vToday,-1)
Last 3 Months|MonthStart(vToday,-3)|MonthEnd(vToday,-1)
Month-To-Date|MonthStart(vToday)|vToday
Year-To-Date|YearStart(vToday)|vToday]
(ansi, txt, delimiter is '|', embedded labels);

Left Join (Ranges)
IntervalMatch (TimeSent)
Load distinct RangeStart, RangeEnd resident Ranges;

Re: Format date/time column to Year, Month, Day, Hour, Minutes

sunny_talwar — Thu, 02 Aug 2018 00:37:07 GMT

What was the issue when you use the above script? It gave you an error or something else went wrong?

Re: Format date/time column to Year, Month, Day, Hour, Minutes

Anonymous — Fri, 03 Aug 2018 12:28:00 GMT

I was able to parse out the data as needed with the following preceding Load statements before my SQL code..

/* Formatting TimeSent to Month, Day, and Year (transform Month to numerical value)*/

LOAD *, num(month(TimeSent)) & '/' & day(TimeSent) & '/' & year(TimeSent) as Date ; /*

Load *,
month(TimeSent) as Month, /* creating Month Field */
day(TimeSent) as Day, /* creating Day Field */
year(TimeSent) as Year /* creating Year Field */