difference time bettween two rows

[neapicture]

In this excel-sheet you see different informations of inbound and outbound calls (incoming time, date account) and the related action.

Now our mission is to add all inbound calls from the same account and the same action which came in within 72 hours

For example: we have to add these two calls (same account and action)

10.04.2017	13:20:11	60000242	In	Stammdaten
11.04.2017	10:57:25	60000242	In	Stammdaten

This example shows, which accounts are not allowed to added (same account, different action)

10.04.2017	13:09:49	60000240	In	Einzug
10.04.2017	13:13:23	60000240	In	Stammdaten

Does anyone knows how can I do that?

Thank y'all,

[Sergey_Shuklin]

Hello, Lutz!

Use Timestamp() function and aggregation to get the hours for each key account+action:

Check - is the amount of hours. After you've got this indicator you'll be able easily separate those inbounds which are under the 72 line.

Script:

t:

LOAD date(date) as date,starttime,acount,[In / Out],action Inline

[

date,starttime,acount,In / Out,action

42740,15:02:48,60000178,Out,Begrüßungsschreiben

42835,13:03:27,60000178,In,Umzug

42835,13:04:01,60000238,In,Einzug

42835,13:09:49,60000240,In,Einzug

42835,13:13:23,60000240,In,Stammdaten

42835,13:20:11,60000242,In,Stammdaten

42836,10:57:25,60000242,In,Stammdaten

];

Left Join(t)

LOAD

acount

,action

,(max(Timestamp(date+starttime))-min(Timestamp(date+starttime)))*24 as check

Resident t

Group by acount,action;

[lawrenceiow]

Have you thought about using Peek?

This function lets you peek at the previous record as the table loads.

So

LOAD *, if(account = Peek('account') and action = Peek('action'), starttime - Peek('starttime'),0) as DifferenceBetweenThisRowAndPreviousRow

In other words - if the account of this row equals the account of previous row AND the action of this row equals the action of previous row then deduct the starttime of the previous row from the starttime of this row to give the difference between this row and previous row.

This expects the data in the excel-sheet to be sorted into the correct order.

[neapicture]

Tx for your answer,

if for example the next call at 14.04.2017 by the same action

(42839,10:57:25,60000242,In,Stammdaten)

All check fields  has the same amount of hours (93,62)

After 72 hours must start a new addition.

Pleas help

[neapicture]

Tx for your answer,

if for example the next call at 14.04.2017 by the same action

(42839,10:57:25,60000242,In,Stammdaten)

All check fields  has the same amount of hours (93,62)

After 72 hours must start a new addition.

Pleas help

[Sergey_Shuklin]

Hello, Lutz!

Don't know if it's exactly what you need, but there is a flag field which slice periods on 72 intervals and mark them to 1,2,3 and so on.

If it's not what you want - please, contact me!

[neapicture]

sorry, it's not exactly what I mean.

The flag mark them not in slice periods an 72h intervals,

Only mark the calls at the first 72 hour - please see the picture - file field new call within 72h)

A re-call after 72 h starts a new period on 72h interval

I must count the re-call whithin  72hour

Thank you for your suport

[Sergey_Shuklin]

Hello, Lutz!

I don't get how you've got a 1-2-3-0-1 line for the 242 account. If you want to count recalls within the 72h intervals than the picture should be like this:

Correct me if I'm wrong.

If I've got you right - PFA.

[neapicture]

Hallo Sergey,

thank you for your support, it works......

how can i filter only the action "Stammdaten" in script

[effinty2112]

Hi Lutz,

               What do you mean by add the two calls? What kind of output are you looking for? You say add together calls within 72 hours of each other. If you have three calls from the same account with the same action and the calls are on Monday, Wednesday and Friday what ones do you add together?

Cheers

Andrew