Week(starttime) AS [Week #], WeekStart(starttime) as Week, Year(starttime) AS Year, Month(starttime) AS [Month #], MonthStart(starttime) as Month, Day(starttime) AS [Day], Weekday(starttime) AS WeekDay, Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear, Week(starttime)&'-'&Year(starttime) AS WeekYear, hour(starttime) as BuildHour, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName,Date
;
Load converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName,Date
;
SQL select starttime,CONVERT(VARCHAR(10),starttime,101) AS Date,BuildID, ((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min, Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName FROM ASWTRANS.DBO.CRMBUILDMETRICS
Do you think it should be something like below ? , But i get an syntax error saying converttolocaltime is not identfied, One basic question , how do the select statement work here , i got an idea of load statements loading , when does a select statement come into consideration ?before or after load statement?
LOAD
Week(starttime) AS [Week #], WeekStart(starttime) as Week, Year(starttime) AS Year, Month(starttime) AS [Month #], MonthStart(starttime) as Month, Day(starttime) AS [Day], Weekday(starttime) AS WeekDay, Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear, Week(starttime)&'-'&Year(starttime) AS WeekYear, hour(starttime) as BuildHour, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName,Date
;
Load converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName,Date
;
SQL select starttime,CONVERT(VARCHAR(10),converttolocaltime(starttime,'Pacific Time (US & Canada)') ,101) AS Date,BuildID, ((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min, Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName FROM ASWTRANS.DBO.CRMBUILDMETRICS
RAMYA_BEGINNER wrote:But i get an syntax error saying converttolocaltime is not identfied, One basic question , how do the select statement work here , i got an idea of load statements loading , when does a select statement come into consideration ?before or after load statement?
ConvertToLocalTime is a QV function. It must be used in the LOAD statement, not in the SQL Select. Only valid SQL may be used in the SQL Select.
The SQL Select is executed first. The data returned by the SQL Select is "passed up" to the LOAD statement.
I changed my Script to below , but i am getting all duplicate values for date, to avoid this i used convert function in select statement previously , as converttolocaltime cannot be used in sql statement and convert(which converts datetime value to date in 101 form) cannot be used in load statement i cannot understand how to go with this...
By the way how do i attach a qlikview file to my post , i tried using insert media , did not work ...
Please Help..
LOAD
Week(starttime) AS [Week #], WeekStart(starttime) as Week, Year(starttime) AS Year, Month(starttime) AS [Month #], MonthStart(starttime) as Month, Day(starttime) AS [Day], Weekday(starttime) AS WeekDay, Date(starttime) as Date, Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear, Week(starttime)&'-'&Year(starttime) AS WeekYear, hour(starttime) as BuildHour, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName
;
Load converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName
;
SQL select starttime,BuildID, ((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min, Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName FROM ASWTRANS.DBO.CRMBUILDMETRICS
Here is my code, i dont get any error with below, even i dont get any data in the field Data
LOAD
Week(starttime) AS [Week #], WeekStart(starttime) as Week, Year(starttime) AS Year, Month(starttime) AS [Month #], MonthStart(starttime) as Month, Day(starttime) AS [Day], Weekday(starttime) AS WeekDay, //Date(starttime) as Date, Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear, Week(starttime)&'-'&Year(starttime) AS WeekYear, hour(starttime) as BuildHour, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName, //date(floor(starttime('starttime', 'MM/DD/YYYY'))) as Date, date(floor(date#('starttime', 'MM/DD/YYYY')))as Date
;
Load converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime, BuildSizeMB,BuildID, Action,Duration,FileCount,ProductLine,Duration_Min, UserName,JobName
;
SQL select starttime,BuildID, ((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min, Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName FROM ASWTRANS.DBO.CRMBUILDMETRICS
All looks correct excpet the "Date" field. It should be simply: date(floor(starttime)) as Date or, if you want to specify format explicitly: date(floor(starttime), 'DD/MM/YY') as Date
Go to Document Properties, tab "Number", and make sure that field Date is Date, not Timestamp nor Time. You can also do this in list box properties, but it will effect only this list box. It's better on document level.
