Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Manipulating Timestamps

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

CS

1 Solution

Accepted Solutions
d_pranskus
Partner
Partner

Hi

I built a sample application. Please have a look.

Cheers

Darius

View solution in original post

9 Replies
whiteline
Master II
Master II

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Try using Interval()

Not applicable
Author

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.

Not applicable
Author

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

whiteline
Master II
Master II

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).

d_pranskus
Partner
Partner

Hi

I built a sample application. Please have a look.

Cheers

Darius

Not applicable
Author

Thanks all for your comments and help!

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