topic Get numeric value of a timestamp? in QlikView

Get numeric value of a timestamp?

datanibbler — Fri, 20 Mar 2015 13:34:06 GMT

Hi,

I need to filter a table

- on the date and

- on the time (but only if the date is yesterday)

=> That is a bit complex. Doable, certainly - but it would be the easier way to just compare the timestamp (which is in numeric format anyway) to the numeric timestamp of the exact date and time I want to set as cutoff.

=> How do I do that?`

Thanks a lot!

Best regards,

DataNibbler

Re: Get numeric value of a timestamp?

datanibbler — Fri, 20 Mar 2015 13:41:45 GMT

Ah, I think I have found out myself.

If I use Makedate() and Maketime() with a BLANK inbetween and put the whole thing into a num() function, I get the numeric value.

It looks quite plausible. Let's see.

Re: Get numeric value of a timestamp?

jonathandienst — Fri, 20 Mar 2015 13:42:21 GMT

Is the cutoff set by one or two variables? Do you mean comparing a timestamp with separate time and date variables? Something like this:

If(TimeStampField < (vDate + vTime)), ....

Assuming vDate and vTime are normal QV date and time data types. Or, if they are text, then something like:

If(TimeStampField < (Date#(vDate, ....) + Time#(vTime, ....))), ....

HTH

Jonathan

Re: Get numeric value of a timestamp?

datanibbler — Fri, 20 Mar 2015 13:47:08 GMT

Hi jonathan,

no. Maybe I explained it badly:

- I have a timestamp in the table in numeric format.

- I just wanted to know how to construct the numeric value (to compare with this) of an exact date and time (that I know, it's not in my table, at least not yet)

Best regards,

DataNibbler

Re: Get numeric value of a timestamp?

Anonymous — Fri, 20 Mar 2015 13:58:42 GMT

Just a warning:

No two timestamps are exactly equal (well, I'm just a little exaggerating). That means, do not use conditions like:

Timestamp1 = Timestamp2.

Using >, >=, <, <= is fine of course.

Re: Get numeric value of a timestamp?

jonathandienst — Fri, 20 Mar 2015 14:25:44 GMT

Just add the date and time. The logic I showed you for variables will work for fields as well.

Jonathan

Re: Get numeric value of a timestamp?

Peter_Cammaert — Fri, 20 Mar 2015 15:32:06 GMT

What has been said below should enable you to find a suitable solution already. To give you some insight in the handling of time/date values internally, check this out:

Date values are integers, time values are fractions (the part of a full day e.g. 1/4 = 0,25 = 6:00:00). Add them together and you get a timestamp value (whether it will really be a dual() value depends on what exactly you are adding together).

Comparisons of - and calculations with timestamps, dates and times will always be done numerically, so adding together MakeDate and MakeTime will produce a purely numerical value that can be compared to any timestamp or datetime field from your database. For example, compare this to Jonathans example using variables (assuming that TimeStampField already contains a numerical datetime):

IF (TimeStampField < (MakeDate(2015, 3, 20) + MakeTime(4, 0, 0)), TrueValue, FalseValue)...

Best,

Peter