How to create a new field based on existing fields?

[Edwin1471]

Hi, how can I combine two fields (2.1 and 2.2) into one field (Main calculation)

I have a table : 

I would like to convert it into something like this :   where START_TIME is the value of 2.1  and the FINISH_TIME is the value of 2.2 field. Completion_time is sum of both two fields  (0.35 + 60.53)

SPL query: 

 | eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  

| eval Process=if(Process="013","2.1 Main calculation",Process)

| eval Process=if(Process="014","2.2 Main calculation",Process)

| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time

| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process

[gcusello]

Hi @Edwin1471,

if the first part of the column headers is static (always 2.1. or 2.2) you can use eval at the end of your search:

| eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  
| eval Process=if(Process="013","2.1 Main calculation",Process)
| eval Process=if(Process="014","2.2 Main calculation",Process)
| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time
| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process
| eval "2.Main Calculation"=case(Process="2.START_TIME","2.1 Main calculation", Process="3.FINISH_TIME","2.2 Main calculation", Process="4.Completion_time","2.1 Main calculation"+"2.2 Main calculation")
| table Process "2.Main Calculation"

 Ciao.

Giuseppe

[Edwin1471]

I am getting this result

[gcusello · Accepted solution]

Hi @Edwin1471,

sorry I made an erro, it isn't a good idea to have spaces or special chars in field names:

| eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  
| eval Process=if(Process="013","2.1 Main calculation",Process)
| eval Process=if(Process="014","2.2 Main calculation",Process)
| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time
| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process
| rename 
   "2.1 Main calculation" AS 21_Main_Calculation 
   "2.2 Main calculation" AS 22_Main_Calculation 
| eval 2_Main_Calculation=case(Process="2.START_TIME",21_Main_Calculation, Process="3.FINISH_TIME", 22_Main_Calculation, Process="4.Completion_time",21_Main_Calculation+22_Main_Calculation)
| rename 2_Main_Calculation AS "2.Main Calculation"
| table Process "2.Main Calculation"

Ciao.

Giuseppe