Time difference between 'OFF' and the immediate next 'ON'.

[ccdyat]

Hi Friends,

I have attached the sample data along with this. I need help to find the Time difference between 'OFF' and the immediate next 'ON'. 

It will be more helpful,if the solution is with In-DB Tools.

Thanks & Regards,

Sreekanth

[mceleavey]

Hi @ccdyat ,

Use a multi-row formula tool and use the following formula:

if [Row-1:Status]="OFF" and [Status]="ON" then DateTimeDiff([Time],[Row-1:Time], "minutes") else 0 endif

[RolandSchubert]

Hi @ccdyat ,

it should be possible using the Multi-Row Formula tool (sample attached), but it's not In-DB ... hope, it's helpful.

Best,

Roland

[OllieClarke · Accepted solution]

Hi @ccdyat here's a way to do it that will work in-db

I should say that it's less efficient than the multi-row method, but I've only used tools that have an in-db equivalent.

Hope that helps,

Ollie

[ccdyat]

Hi @OllieClarke,

Can you please help me to do the same with MultiRow Formula as well? 

Thanks & Regards,

Sreekanth

[mceleavey]

Hi @ccdyat ,

it would be exactly the same aside from you would use a formula tool, and rather than doing the difference between a field and the same field row-1, you would apply the difference between two columns. 

Post some data and I'll build it for you.

M.

[ccdyat]

Hi @mceleavey ,

Please find the attached data. Below the requirement.

Thanks.

[mceleavey]

Oh, sorry. I thought you meant you had a different data set that was laid out differently.

With that data set you would not use multi-field formula.

M.

[OllieClarke]

Hi @ccdyat there is no in-db multi-row formula i'm afraid (which is why I had to join and summarise instead)

[ponraj]

Sample workflow