I have two tables to calculate a response time value. It works like this: A job (e.. job number 98712) gets created (timestamp A). Then this job undergoes various workflow status to closure with the first one being ‘job status set to Connect: Onsite’ – timestamp B. The response time is timestamp B – A.

Now it’s possible that there are a few trips until this job gets closed and every trip has its own onsite timestamp as you can see in the example where the technician is onsite 4 times. So I need to load the very first entry (i.e. 1 May 9:31). The others 3 in this case are irrelevant. I create merge query to pull in this data based on job number.

How can I either update the load criteria or transform the data so that the calculation takes the first timestamp (timestamp B)

​I have two tables to calculate a response time value. It works like this: A job (e.. job number 98712) gets created (timestamp A). Then this job undergoes various workflow status to closure with the first one being ‘job status set to Connect: Onsite’ – timestamp B. The response time is timestamp B – A. Now it’s possible that there are a few trips until this job gets closed and every trip has its own onsite timestamp as you can see in the example where the technician is onsite 4 times. So I need to load the very first entry (i.e. 1 May 9:31). The others 3 in this case are irrelevant. I create merge query to pull in this data based on job number. How can I either update the load criteria or transform the data so that the calculation takes the first timestamp (timestamp B)    Read More

​