topic date() Function with Timestamp in QlikView

date() Function with Timestamp

Wed, 17 Jun 2009 04:47:59 GMT

Hi all,

I'm using QlikView 9.0 to do a breakdown of the membership on this site, and I've run into a little bit of a snag using the date() function. The site logs new users' join date by timestamp, like this:


2009-06-11 18:42:48

I don't particularly care about the time, but I want to see how many folks joined on each day, so I wrapped that field in a date() function to strip away the time.

The problem is, all of the dates left behind are showing up as individual values, not the same value (i.e, 4/15/2009 shows up multiple times instead of once in the list box):

I imagine it's doing that because of the time that's omitted; since they are different, they are being added as different values.

Can someone please help me with the syntax needed to make these values the same, at least in QlikView? Thanks!

date() Function with Timestamp

rwunderlich — Wed, 17 Jun 2009 05:06:53 GMT

Hi Jason,

date() is a display formatting function, and does not change the underlying value. The fractional part, representing the time, is still there in each value.

The typical way to get just the date from a timestamp is the floor() function. e.g floor(mytimestamp) as mydate.

-Rob

date() Function with Timestamp

Wed, 17 Jun 2009 05:18:12 GMT

Rob, as always, you populate the forum with your wisdom. it worked perfectly! Thank you.

date() Function with Timestamp

johnw — Wed, 17 Jun 2009 06:30:50 GMT

And similarly, use frac(mytimestamp) to get just the time. Floor(x)+frac(x) = x. I figure many people would want to extract both a date and time from a timestamp, even if you don't need it in this specific case.

date() Function with Timestamp

Wed, 17 Jun 2009 06:32:33 GMT

John,

That could be cool, to see which times of day people join the site. Thanks for your input as well!

date() Function with Timestamp

Anonymous — Wed, 17 Jun 2009 07:43:30 GMT

Jason,
It's been a sort of a standard in our company to use date(floor(Field)) in the script. Besides preventing the problem like the one you had, it also makes the QV application smaller because of the small number of distinct values in the date field. Cant't be more than 366 per year. With the fractional part, virualy any record is distinct. Not good if you have millions of records.
Not the case with QlikCommunity users yet. But you can see the difference in the file size, I'm sure.