Tableau : Find difference between timestamps included in string

Question

I have a csv file with this test time/dates formatted as strings concatenated with either 'parsing started/ended at' along with file_id in a separate field. The screenshot of data is-

For each file_id I have to calculate difference between fist parsing start time and parsing end time. My problem is that there are not exactly two or three other parsing start time for each file_id so that I may match the first record with fourth or fifth or third record with each file_id. How can I achieve this in tableau.

Thanks in advance.

Answer 1

Do It like this.

Step-1 Create calculated field1 split1 like this:

TRIM( SPLIT( [STARTE OR ENDED], ":", 1 ) )

step-2 Create calc field 2 timestamp like this

DATEPARSE ( "yyyy-MM-dd HH:mm:ss", 
TRIM( SPLIT( [STARTE OR ENDED], "at:", -1 ) ) )

create calc field 3 'st_or_end` like this

TRIM( SPLIT( SPLIT( [Split 1], "Parsing", 2 ), "at", 1 ) )

create a calc field LOD event start date as

{FIXED [FILENAME]: MIN(
IF [st_or_end] ='started' THEN [timestamp] END)}

create second calc field LOD event end date as

{FIXED [FILENAME]: MIN(
IF [st_or_end] ='ended' THEN [timestamp] END)}

create your desired field time_taken_in_seconds like this

DATEDIFF('second', [Event Start date], [event end date])

Get your desired view

A little tweak can get you view like this too