topic Manipulating Timestamps in QlikView

Manipulating Timestamps

Wed, 23 Apr 2014 11:45:24 GMT

Hi all,

I'm having a bit of trouble calculating a "period" field based on two timestamps: a reference time stamp for the end of a period and the time stamp of an event.

Left Join(AttComp_Imp)

LOAD *

,if([Elapsed Time]<[Period 1 End],1

,if([Elapsed Time]<[Period 2 End],2

,if([Elapsed Time]<[Period 3 End],3

,if([Elapsed Time]<[Period 4 End],4

,if([Elapsed Time]<[Period 5 End],5

,if([Elapsed Time]<[Period 6 End],6

,if([Elapsed Time]<[Period 7 End],7

,8)

)

) As [Match Period]

Resident AttComp_Imp;

The resulting values are all null - and have been when I have tried with Time(), Time#(), Timestamp() and Timestamp#() around the individual elements and the calculation as a whole.

In a chart table, the calculations work. I need to do the calculation in the script.

Thanks

Re: Manipulating Timestamps

whiteline — Wed, 23 Apr 2014 12:39:31 GMT

Hi.

It seems that you Time and Period fields are just text and should be converted to dates with # function.

*#() functions convert text into date while others just change the text format of date.

Post the source data.

Re: Manipulating Timestamps

Wed, 23 Apr 2014 12:45:13 GMT

Source data cannot be provided but it comes from an excel which is timestamp format.

Re: Manipulating Timestamps

Wed, 23 Apr 2014 13:01:14 GMT

As an example:

Table 1:

Event, Time

1, 00:10:02

2, 00:57:13

Table 2:

Period, End Time

1, 00:15:00

2, 00:45:00

3, 01:00:00

Re: Manipulating Timestamps

Wed, 23 Apr 2014 13:10:47 GMT

Try using Interval()

Re: Manipulating Timestamps

Wed, 23 Apr 2014 13:16:38 GMT

I have tried

Interval(Time([Elapsed Time])-Time([Period 1 End]))

And

Interval([Elapsed Time]-[Period 1 End])

and both return null values when used in the script (they both work in charts).

I have declared the fields as Time() on import as well.

Re: Manipulating Timestamps

Wed, 23 Apr 2014 13:21:52 GMT

easiest way to deal with it would be to convert them to numbers to achieve what you are after Callum

Re: Manipulating Timestamps

whiteline — Wed, 23 Apr 2014 18:03:56 GMT

Hi.

So the Time values are just strings.

Use something like Time#([End Time], 'hh:mm:ss') to convert the strings into timestamps to be able to compare them with <>.

Again, Time() changes the text format of dual value (both number and text), while Time#() converts the string into timestamps (dual).

Re: Manipulating Timestamps

d_pranskus — Wed, 23 Apr 2014 18:33:35 GMT

I built a sample application. Please have a look.

Cheers

Darius

Re: Manipulating Timestamps

Thu, 24 Apr 2014 15:41:41 GMT

Thanks all for your comments and help!

Darius, the solution used in the example has worked perfectly. Thanks very much!