0

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.

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
DrGenius
  • 817
  • 1
  • 9
  • 26
  • Are there just 4 rows in your sample? shouldn't be the second tow the one to be conisedered due to its time stamp (08:20 vs 08:24)? – Fabio Fantoni Oct 15 '20 at 09:23
  • @FabioFantoni No, its the first with the forth (08:24 vs 08:24). The main problem is that I want to substract two timestamps that are in the same column. – DrGenius Oct 15 '20 at 09:31
  • subctracting values form 2 timestamp in the same column is not the problem... actually it's quite easy if you share the logic in order to select those rows among all the others – Fabio Fantoni Oct 15 '20 at 09:37
  • @FabioFantoni Yes this is the problem, i sorted them alphabetically so, the first started has to be substracted with the first ended, the second stared with the second ended etc... And in the case that a started does not have ended then has a special value(something to show that its ongoing) . – DrGenius Oct 15 '20 at 09:52
  • So you mean there are more than 2 values connected with some same record/id? so that only first value from start and end value be compared? It would have been really easier if you could have shown some more of your data. Please share a csv if you can? – AnilGoyal Oct 15 '20 at 11:29
  • How many of maximum 'parsing started' records are there in any one of the record_id? – AnilGoyal Oct 15 '20 at 11:31

1 Answers1

0

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

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
  • STARTE OR ENDED FILENAME Parsing started at: 2020-10-15 07:15:13 ID_1 Parsing started at: 2020-10-15 08:20:30 ID_1 Parsing started at: 2020-10-15 08:24:52 ID_1 Parsing ended at: 2020-10-15 08:24:52 ID_1 Parsing started at: 2020-10-13 19:50:12 ID_2 Parsing ended at: 2020-10-13 19:54:39 ID_2 Parsing started at: 2020-10-15 07:14:05 ID_3 Parsing started at: 2020-10-15 07:16:56 ID_3 Parsing ended at: 2020-10-15 07:16:56 ID_3 – DrGenius Oct 15 '20 at 12:07
  • Though a better presentation of data was expected, yet and nevertheless, I have edited the solution completely. Upvotes will be highly appreciated, if my solution served the purpose. – AnilGoyal Oct 16 '20 at 04:47