topic Pivot Table - working with strings in QlikView

Pivot Table - working with strings

Mon, 28 Jan 2013 09:53:06 GMT

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 — Mon, 28 Jan 2013 09:57:56 GMT

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

Mon, 28 Jan 2013 14:27:31 GMT

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 — Mon, 28 Jan 2013 14:56:23 GMT

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

will give you 11355

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

will give you 161355