Pivot Table - working with strings

[#1]

Hi all,

I am very new to Qlickview and would much appreciate some help !

I am trying to create a pivot table that tracks activity.

The data contains the following fields (all strings) :

- Activity

- Date Occurred (format = yyyymmdd)

- Time Occurred (format = hhmmss - if the hour is a single value ie 01 then the format would be hmmss -11355 = 01:13:55)

I would like the pivot table to :

- Group by Date (i have this working)

- Show activity (i have this working)

- Order by the time (i have this working)

- For each day show the amount of time between the first activity and the last activity (i am stuck on this)

Any help would be much appreciated !!!

Thanks

Martin

[sujeetsingh]

Hi we can use calculated dimensions and use interval() function to find the day diff.

[#3]

Hi,

Thanks for your response.

I am having trouble with the interval() function because of the format of my time fields (Its a string).

Eg : 11355 - 161355

This should be 01:13:55 - 16:13:55

Is it possible for the interval function to understand the format.

Or do i need to do some string manipulation myself.

If the answer is string manipulation do you have any tips ?

My main problem is the variability between a 5 an 6 char string stops me using a standard function eg Right('11355',2)

Thanks for your help

Martin

[RedSky001 · Accepted solution]

subfield('11355 - 161355','-',1)

will give you 11355

subfield('11355 - 161355','-',2)

will give you 161355