timestamptableau-apilevel-of-detail

Tableau : Find difference between timestamps included in string


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-

enter image description here

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.


Solution

  • 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

    view

    A little tweak can get you view like this too

    enter image description here