topic Re: Timestamp calculation in QlikView

Timestamp calculation

Tue, 08 Nov 2016 10:50:51 GMT

Hi guys,

please I have this timestamp format in my database timestamp= 2016-11-01 00:00:00.0000000 +00:00 and I convert it to this format Date = 2016-11-01 by using this expression : Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD')

I want whenever I choose a date for example 2016-11-01, I want to get the value of this timestamp 2016-11-01 05:59:57.1810000 +00:00.

Please help me.

Re: Timestamp calculation

MindaugasBacius — Tue, 08 Nov 2016 12:06:39 GMT

The expression have to be in a script not in front end:

Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') as Date

Re: Timestamp calculation

Tue, 08 Nov 2016 12:19:19 GMT

Yes Mindaugas, the expression is in the script and not in front end.

Re: Timestamp calculation

MindaugasBacius — Tue, 08 Nov 2016 13:30:58 GMT

Is the field that contains 2016-11-01 05:59:57.1810000 +00:00 data are also in the script and in the same table?

Re: Timestamp calculation

Tue, 08 Nov 2016 14:50:16 GMT

yes they in the same table

Re: Timestamp calculation

MindaugasBacius — Tue, 08 Nov 2016 14:53:16 GMT

So simple make Table box containing [Date] and [Full Date] fields.

You will see how they are connected.

Re: Timestamp calculation

rubenmarin — Tue, 08 Nov 2016 14:57:48 GMT

Hi Lou, you want to sum that exact time part to the date? weird, but it's what I understand from the post... if that's the case you can create a variable wich contains the time part to sum to the dates:

LET vTimeToSum = Num(Time#('05:59:57.5810000', 'hh:mm:ss.fffffff'));

And create the field with the timestamp:

Timestamp(Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00')

If it's a different field of your date field you can use this field in expressions and leave the date field as selectable field.

Re: Timestamp calculation

Tue, 08 Nov 2016 17:21:13 GMT

Hi Ruben,

I tried your expression: Timestamp(Date#(SubField(timestamp, ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00') as DD

But when I choose a value for example 2016-11-01 05:59:57.5810000, it gives me the sum of all the values of the date 2016-11-01 and not only the value of this timestamp 2016-11-01 05:59:57.5810000

Re: Timestamp calculation

rubenmarin — Wed, 09 Nov 2016 08:07:27 GMT

Hi Lou, this was intended to be in the script (I read above it was on script), creating the fields while loading the data so there will be a selectable field with only the date and a related field with the timestamp.

I don't see why sum all the values, is there a group by or something than loads many dates in one record?

Is there a possibility to upload a sample to make some tests?

Re: Timestamp calculation

Wed, 09 Nov 2016 11:19:12 GMT

Hi Ruben,

I attached a data sample.

You will find and the field I want to calculate is : VTagResolvedVirtual.ValueFloat

So when I choose a value frim the field 'da', for example 2016-11-01, it should give me the value of the field 'VTagResolvedVirtual.ValueFloat' that corresponds to this timestamp '2016-11-01 05:59:57.1810000 +00:00', but what I get is the sum of value of the field 'VTagResolvedVirtual.ValueFloat' of all the timestamps of this date 2016-11-01.

Re: Timestamp calculation

rubenmarin — Wed, 09 Nov 2016 11:50:46 GMT

Hi Lou, what I see is a text box with the expression "=sum(VTagResolvedVirtual.ValueFloat)", so it sums all the values because the expression tells QV to sum the dates, you can use Min() or Max() to retrieve only one value.

Another option can be creating another table that loads the different dates and create the timestamp in this table, so there will be only one timestamp for each different date:

LOAD Distinct VTagResolvedVirtual.ValueFloat,

Timestamp(Date#(SubField(VTagResolvedVirtual.TimeStamp, ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00') as DD

Resident ...

Re: Timestamp calculation

Wed, 09 Nov 2016 14:08:41 GMT

Hi Ruben,

I tried before the expression : =Sum({<DD = {">=$(=Timestamp(Floor(Min(DD))+num(Maketime(05:59:57))))<=$(=Timestamp(Floor(Max(DD))+num(Maketime(05:59:57)))))"}>}VTagResolvedVirtual.ValueFloat)

but it didn't work, it also gives me the some for all the timestamps of the chosen date.

Re: Timestamp calculation

rubenmarin — Wed, 09 Nov 2016 14:25:26 GMT

Hi Lou, that's what I said above, if you use Sum() QV will sum, and if it has x times the same value it will summ the x values, the option to add another table is to load only one value of each date, ahve you created this related table?

If you created the table maybe it's still retrieving more than one value for each date... there is only one VTagResolvedVirtual.TimeStamp for each VTagResolvedVirtual.ValueFloat? In that case maybe forcing to store only one value:

LOAD VTagResolvedVirtual.ValueFloat,

Timestamp(Date#(SubField(Min(VTagResolvedVirtual.TimeStamp), ' ', 1),'YYYY-MM-DD') + $(vTimeToSum), 'YYYY-MM-DD hh:mm:ss.fffffff +00:00') as DD

Resident ...

Group By VTagResolvedVirtual.ValueFloat;

The Min() should be a MinString() if VTagResolvedVirtual.TimeStamp is a string.

Re: Timestamp calculation

Wed, 09 Nov 2016 16:11:11 GMT

Hi ruben,

I tried you solution and I created the table and I used group by, but I still get the sum of all the timestamps