topic Re: SQL TRUNC style function in Qlikview in QlikView

SQL TRUNC style function in Qlikview

Mon, 26 Aug 2013 13:13:11 GMT

Hi Folks, I am looking for SQL TRUNC style function to truncate the time stamp values.

Thanks in Advance...

Re: SQL TRUNC style function in Qlikview

disqr_rm — Mon, 26 Aug 2013 13:14:47 GMT

floor() ?

Re: SQL TRUNC style function in Qlikview

rajeshvaswani77 — Mon, 26 Aug 2013 13:18:51 GMT

Hi Dathu,

Please use Yearstart function.

yearstart ( '2001-10-19' ) returns '2001-01-01'

yearstart ( '2001-10-19', -1 ) returns '2000-01-01'

yearstart ( '2001-10-19', 0, 4 ) returns '2001-04-01'

thanks,

Rajesh Vaswani

Re: SQL TRUNC style function in Qlikview

tresB — Mon, 26 Aug 2013 13:18:56 GMT

Like TRUNC with different formats (YEAR, Q, MONTH....), you won't get all in one in QV, but you can try separately,

YearStart(), MonthStart(), WeekStart() functions.

Re: SQL TRUNC style function in Qlikview

pgrenier — Mon, 26 Aug 2013 13:32:39 GMT

Good morning Dathu,

I would invite you to use two functions together Date() and Floor(). Using Floor() on a date will truncate the hours, minutes, seconds and miliseconds from the date, but will also convert it to a numerical value. Hence, the need for wrapping it with the Date() function to bring it back to a date format.

LOAD Date(Floor(TIMESTAMP_FIELD)) as Date_Field

From ...

Regards,

Philippe

Re: SQL TRUNC style function in Qlikview

Mon, 26 Aug 2013 13:37:24 GMT

Thanks for reply's. But specifically looking how to truncate the timestamp value to hours and minutes.

TRUNC(TIMEFIELD,'HH24') and TRUNC (TIMEFIELD,'MI')

Re: SQL TRUNC style function in Qlikview

tresB — Mon, 26 Aug 2013 13:41:43 GMT

try:

Timestamp(TIMEFIELD, 'hh') , and

Timestamp(TIMEFIELD, 'mm')

Re: SQL TRUNC style function in Qlikview

pgrenier — Mon, 26 Aug 2013 13:54:45 GMT

Otherwise, if you are looking to keep the date while eliminating the seconds, your could either use the following expression:

Date(Floor(TIMEFIELD) + Hour(TIMEFIELD)/24 + Minute(TIMEFIELD)/(24*60), 'YYYY-MM-DD hh:mm:ss')

Date(Floor(TIMEFIELD, 1/(24*60)), 'YYYY-MM-DD hh:mm:ss')

There is a possibility to define the base for the floor fonction, so if to define the base to 1/(24*60), it shall truncate the seconds. In the same manner, using the 1/24 base shall truncate your dates to the hours, and going for 1/(24*60*60) will eliminate the milliseconds.

Hope this helps,

Philippe

Re: SQL TRUNC style function in Qlikview

Mon, 26 Aug 2013 13:57:18 GMT

I tried this one in script level. But this will give full timestamp whenever I can use the Timestamp function.

So I used this one : Timestamp#(text(timestamp(TIMEFIELD,'MM/DD/YYYY h:mm')))

But it kills the time because I am working on very lagre dataset like 16M rows.

Re: SQL TRUNC style function in Qlikview

orital81 — Mon, 26 Aug 2013 14:01:48 GMT

Try :

Hour(TIMEFIELD) , and Minute(TIMEFIELD)